DAX Formula- % of Total

[bravo1563]

Hello
I'm trying to write a PowerPivot DAX formula to get the percentage of total based on a subcategory. Ideally I'd like to click on a slicer and have to formula automatically calculate the % of total. It seems like an easy formula, however I've not been able to get it to work. Below is the formula I've been trying to get to work. Any help would be greatly appreciated! I've included a link to the file.

% of Total= CALCULATE([Sum of Runs],Table1,Table1[Sub Category]=[Sub Category])

Example Data:
[TABLE="width: 316"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]League[/TD]
[TD]Team[/TD]
[TD]Sub Category[/TD]
[TD]Runs[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Atlanta[/TD]
[TD]NL East[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Philadelphia[/TD]
[TD]NL East[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Pittsburgh[/TD]
[TD]NL Central[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]St Louis[/TD]
[TD]NL Central[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Arizona[/TD]
[TD]NL West[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]Seattle[/TD]
[TD]AL West[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Washington[/TD]
[TD]NL East[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Miami[/TD]
[TD]NL East[/TD]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]

 

[masterelaichi]

What are you trying to do to get the %? Are you calculating the percentage from the pivot table directly, by doing a "% of column total" ?

By looking at your formula, the expression [sum of runs] will return a number. Replace that formula that returns a percentage and it should work. You may need to use a temporary measure. Btw, I cannot find the link to the file

 

[akice]

You can also play around with adding the "Runs" measure to the pivot a second time and right-click on column header and choose "Show Values As" submenu. It may display as you are wanting.

 

[bravo1563]

To clarify I'd like to do this as a formula because I'm going to be building upon it. I'm aware of changing the value to display as a % of total. Ideally I want to be able to click on the subcategory (division) slicer and have it calculate as a % of total. For example if I were to click on NL East I would expect to see the results below. Does anyone know of a PowerPivot (DAX) formula to do this calculation? Any help would be great!

[TABLE="width: 404"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]League[/TD]
[TD]Team[/TD]
[TD]Sub Category[/TD]
[TD]Runs[/TD]
[TD]% of Total[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Atlanta[/TD]
[TD]NL East[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]41%[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Philadelphia[/TD]
[TD]NL East[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Washington[/TD]
[TD]NL East[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]National[/TD]
[TD]Miami[/TD]
[TD]NL East[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]32%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NL East[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[masterelaichi]

Try this..

Total Runs scored:=(CALCULATE(SUM(Table1[Runs]),ALL(Table1)))

% Runs:=DIVIDE(SUM(Table1[Runs]),[Total Runs scored])

Edit: don't forget to change the number format to percentage

 

[bravo1563]

Hi Master,
It's close but I need it to intuitively calculate based on the subcategory (Division) that's selected via the slicer. Right now it's calculating as a % of total of all values but I need it to calculate as a % of the subcategory (division). In my example the %s should always add to 1 or 100% no matter what slicer I click on.

 

[akice]

Change the ALL() to ALLSELECTED()

 

[bravo1563]

Awesome thanks akice works perfectly!!