Percentage value of a segment against segment total Power BI(DAX)

user_5289665 0
Viewed 425

enter image description here

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.

dax powerbi
1 Answers

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:

Matrix Visual

The measure I use here is

%ItemTotal =
DIVIDE (
    SUM ( Table1[count] ),
    CALCULATE ( SUM ( Table1[count] ), ALLEXCEPT( Table1, Table1[item_no] ) )
)

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".

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.

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
    )
)

See here and here for other ways to modify the filter context instead of ALLEXCEPT.

user_7652260
Related