CODEWITHSUNDEEP
excelpivot-tabledaxpowerpivot
Haithamdar Dar
Haithamdar Dar

Reputation: 11

How I can find percentage out of subtotals with multiple columns in Pivot table

How I can find percentage out of subtotals with multiple columns in Pivot table.

PercentYes :=
    CALCULATE ( SUM ( MyTable[value] ), MyTable[answers] = "yes" ) /
    CALCULATE (
        SUM ( MyTable[value] ),
        ALL ( MyTable[subcategory], MyTable[answers] )
    )

enter image description here

Upvotes: 0

Views: 382

Answers (1)

Ellen S
Ellen S

Reputation: 106

I created a sample of values

enter image description here

I would first create a sum measure:

Sum of Values:=SUM(MyTable[Value])

Then, I would create the percentage measure:

Percent of Values :=
DIVIDE (
    [Sum of Values],
    CALCULATE (
        [Sum of Values],
        ALL ( MyTable[Subcategory],MyTable[Answers] )            
    )
)

Using DIVIDE will help with error trapping zeroes in the denominator. The result looks like this:

enter image description here

Upvotes: 1

Related Questions