Calculating Percentages

[Anonymous]

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	Australia	30
Sales	Norway	80
Sales	India	10
Sales	China	30

Now, I need 2 additional columns (Cols 4 & 5 in the table below), that will help me calculate the percentage - something like this!

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

Can someone help me do that? Also, please note that there are multiple departments in the department column. The Total ppl column should contain values for each department! 

Thanks in advance 🙂 

[Simona0926]

Hey all,

I am trying to find a measure that would help me to see the percentage of each status for each AREA.

Example: I have a total of 40 tickets from which 20 on WE and 20 on MEA.

I want to see what % of the total on WE are completed.

At this moment I have the option to see only the % of the total (40 tickets).

I would like to highlight the fact that the chart used is columns (not tables).

Thank you so much in advance.

[Sean · Accepted solution]

@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!

[Anonymous]

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

[Anonymous]

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

[Sean]

@Anonymous

Formulas revised above!

[Anonymous]

That works! Thanks a ton!!! 🙂