跳到主要内容

组内占比报表

概述​

组内占比：在分组中计算某个单独的数据，在总量中占据的比例。

应用场景​

如下图所示，计算不同商品中每个季度销售额在总销售额的占比。

image
Preview

实现思路​

通过下列公式实现组内占比计算
=C2/SUM(C2[!0]{A2=$A2})

示例说明​

数据准备​

在数据面板中添加数据集，可选择Json数据集和Http接口数据集，Json数据如下图所示：

[{"商品":"A产品","季度":1,"销售额":5890.56},{"商品":"A产品","季度":2,"销售额":4566.26},{"商品":"A产品","季度":3,"销售额":7861.56},{"商品":"A产品","季度":4,"销售额":5654.56},{"商品":"A产品","季度":1,"销售额":5820.96},{"商品":"A产品","季度":2,"销售额":5666.26},{"商品":"A产品","季度":3,"销售额":8661.56},{"商品":"A产品","季度":4,"销售额":5254.56},{"商品":"B产品","季度":1,"销售额":5890.56},{"商品":"B产品","季度":2,"销售额":4456.16},{"商品":"B产品","季度":3,"销售额":7561.56},{"商品":"B产品","季度":4,"销售额":3554.56},{"商品":"B产品","季度":1,"销售额":4620.96},{"商品":"B产品","季度":2,"销售额":5456.26},{"商品":"B产品","季度":3,"销售额":8891.56},{"商品":"B产品","季度":4,"销售额":5623},{"商品":"C产品","季度":1,"销售额":4600.78},{"商品":"C产品","季度":2,"销售额":5246},{"商品":"C产品","季度":3,"销售额":7761.56},{"商品":"C产品","季度":4,"销售额":8164},{"商品":"C产品","季度":1,"销售额":4645.56},{"商品":"C产品","季度":2,"销售额":6579},{"商品":"C产品","季度":3,"销售额":7591.56},{"商品":"C产品","季度":4,"销售额":7168}]

image
Preview

报表设计​

将数据字段依次对应拖拽到对应单元格中，并将A2、B2、C2单元格的表格设置为分组，并按照图中颜色方框框选分组。如下图所示：

image
Preview

组内占比的公式说明：
C2/SUM(C2[!0]{A2=$A2})
SUM()：计算括号里面所有数据的总和;
C2[!0]：输出满足大括号里面条件的所有数据；
A2 = $A2：将计算限定在每个分组内，即每个商品各自统计自己的。

效果预览​

组内占比演示.json

image
Preview