DAX Formula- % of Total

bravo1563

New Member
Joined
Dec 25, 2013
Messages
20
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:
LeagueTeamSub CategoryRuns
NationalAtlantaNL East43
NationalPhiladelphiaNL East16
NationalPittsburghNL Central46
NationalSt LouisNL Central12
NationalArizonaNL West30
AmericanSeattleAL West33
NationalWashingtonNL East12
NationalMiamiNL East34

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!

LeagueTeamSub CategoryRuns% of Total
NationalAtlantaNL East4341%
NationalPhiladelphiaNL East1615%
NationalWashingtonNL East1211%
NationalMiamiNL East3432%
NL East105100%

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top