Calculating % of Grand Total in Pivot Tables

0
votes

I have a pivot table in Excel.

My dataset includes workforce numbers for the 32 states in the country, which are grouped in six different regions. This data set is divided into quarters since 2015 to date.

I need this table to show both the % of total workforce at a national level, and at a regional level.

I have tried using calculate, sum, allselected, but then if I use an slicer and show just one region, I end up with the % of workforce within that region and not at a national level.

I am a beginner in DAX and using Power Pivot, how can I calculate the total workforce at a national level for a given period so that it doesn't change if I change the region? Please help!

1
pivot-tabledaxpowerpivot
Welcome to Stack Overflow. If you haven't already, make sure to check out How to Ask and help center for more info on how SO works. Good luck! – 0xCursor

1 Answers

1
votes

Let's say you have a measure called "Total Workforce" (whatever it is - you haven't provided any information about its meaning).

To calculate total workforce at a national level, you need to create another measure:

National Workforce = CALCULATE( [Total Workforce], ALL(Table[Region]))

where Table is your table name, and Region is the name of the field containing regions. Function ALL in DAX removes all filters from a column, a list of columns, or an entire table.

Then, to calculate the ratio:

Workforce % of National = DIVIDE( [Total Workforce], [National Workforce])

Please note: it's very hard to answer questions like that without seeing your data, data model, formulas and the description of the desired outcome. It'd be great if you could provide this information.