分部相对于分部总Power BI(DAX)的百分比值 [英] Percentage value of a segment against segment total Power BI(DAX)
问题描述
大家好,我是Power BI(DAX公式)的新手,我试图计算其中类别" = X和"item_no" = 1的计数"总和对计数"总数的百分比贡献"item_no" = 1的所有类别中的".理想的数学陈述是(30/50)* 100%我打算在图表中表示百分比值,该图表以上述示例中表示的格式显示每个不同的item_no对其总数的百分比贡献.
Hi guys, I am new to Power BI(DAX formulas) and I am attempting to calculate the percentage contribution of the sum of "count" where "category" = X and "item_no"=1 to the total of "count" across all categories where 'item_no' = 1. The ideal mathematical statement here will be the (30/50)*100% I intend to represent the percentage values in a chart showing percentage contribution of each distinct item_no to its total in the format as represented in the example above.
推荐答案
解决此问题的标准方法是
The standard way to approach this is
calculation over partial set / same calculation over larger set
由于您尚未明确说明要计算的上下文,因此我认为这是沿着这些方向的视觉效果:
Since you haven't made it clear what context you are trying to calculate this, I will assume it's a visual along these lines:
我在这里使用的度量是
%ItemTotal =
DIVIDE (
SUM ( Table1[count] ),
CALCULATE ( SUM ( Table1[count] ), ALLEXCEPT( Table1, Table1[item_no] ) )
)
在分子中,本地过滤器上下文中的总和为 .例如,在该左上角的单元格中,这意味着匹配 item_no = 1
和 category ="X"
的所有行.
In the numerator, you have the sum in the local filter context. For example, in that top-left cell, this means all rows that match item_no = 1
and category = "X"
.
在分母中,除了删除 all 过滤器上下文除我们要保留的上下文( item_no
)外,我们执行相同的操作它包含所有 category
值.
In the denominator, we do the same thing except we remove all filter context except the context we say to keep (item_no
) so it includes all category
values.
如果您试图在视觉环境之外计算60%,则可以明确定义所需的过滤器.例如,这可以在任何过滤器上下文中使用:
If you're trying to calculate that 60% outside of the context of a visual, then you can explicitly define what filters you want. For example, this should work in any filter context:
X%_Item1 =
DIVIDE (
CALCULATE (
SUM ( Table1[count] ),
ALL ( Table1 ),
Table1[category] = "X",
Table1[item_no] = 1
),
CALCULATE (
SUM ( Table1[count] ),
ALL ( Table1 ),
Table1[item_no] = 1
)
)
在此处和 ALLEXCEPT
.
See here and here for other ways to modify the filter context instead of ALLEXCEPT
.
这篇关于分部相对于分部总Power BI(DAX)的百分比值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!