CODEWITHSUNDEEP
powerbi
Emerald
Emerald

Reputation: 23

Percentage for each subgroup

I have made an Matrix ("pivot table") in Power BI. The matrix have 3 columns, were the first column is groups of attributes. 2nd column is the attributs and the 3rd column is a count of number of attributes. Below on every column, there is a total count.

enter image description here

I want to add an additional column to state how much % each count represent for each group.

I have tried to code this in DAX but it seems that the code only calculate the percentage of the grand total, and not for each subtotal.

%Percentage =
      COUNT ( Table1[Counter_number] ) /
      CALCULATE (
        COUNT ( Table1[Counter_number] );
        ALLEXCEPT ( Table1; 'Table2'[Type] )
    )

Upvotes: 2

Views: 10567

Answers (1)

RADO
RADO

Reputation: 8148

The reason you see % of grand total instead of subtotal is the wrong column in ALLEXCEPT. Change your code to this:

%Percentage =
      DIVIDE(
        COUNT ( Table1[Counter_number] ),
        CALCULATE (
          COUNT ( Table1[Counter_number] );
          ALLEXCEPT ( Table1; 'Table2'[Group] )
      )

ALLEXCEPT needs to preserve "Group" filter, not "Type". Think about how "Subtotal" cell is calculated: you need to count all type per 1 Group. Hence "ALL, except GROUP".

Upvotes: 2

Related Questions