Forum Discussion
Calculating Percentages
So I have this calculated table (I derived the table using a CALCULATE()), that looks like this. Dept Location No of Ppl Sales USA 40 Sales Denmark 20 Sales Sweden 40 Sales...
Anonymous
Okay if you really want COLUMNS here are those formulas...
Total People COLUMN = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept]) ) Percentage COLUMN = DIVIDE ( 'Table'[No of Ppl], 'Table'[Total People COLUMN], 0 )But in case you want MEASURES
Total People MEASURE = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept] ) ) Percentage MEASURE = DIVIDE ( SUM('Table'[No of Ppl]), [Total People MEASURE], 0)Hope this helps!
Good Luck! :smileyhappy:
Hey Sean,
I'm looking only for Columns. But the SUM() formula to calculate no of people doesn't seem to help if I have more than 1 department. I'm sending you a sample below.
| Dept | Location | No of Ppl | Total Ppl | Percentage |
| Sales | USA | 40 | 250 | 0.16 |
| Sales | Denmark | 20 | 250 | 0.08 |
| Sales | Sweden | 40 | 250 | 0.16 |
| Sales | Australia | 30 | 250 | 0.12 |
| Sales | Norway | 80 | 250 | 0.32 |
| Sales | India | 10 | 250 | 0.04 |
| Sales | China | 30 | 250 | 0.12 |
| HR | USA | 20 | 137 | 0.15 |
| HR | Denmark | 10 | 137 | 0.07 |
| HR | Sweden | 50 | 137 | 0.36 |
| HR | Australia | 20 | 137 | 0.15 |
| HR | Norway | 25 | 137 | 0.18 |
| HR | India | 10 | 137 | 0.07 |
| HR | China | 2 | 137 | 0.01 |
Hi Shirley,
I have the same issue, as you did. Just with "Used budget" and "Budget", where I want to find the "Percentage used" for each department.
I'tried the formules under Colums as mentioned above, but it doesn't work. Can you show mewhat you wrote in the formula bar to get your percentages?
Regards Jonas