大家好,我是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)的百分比值
发布评论