分部相对于分部总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屋！