How to calculate the ratio of SUM of Total Employee Divide by Sum of Total Plan by each department

[Anonymous]

Hi everyone, i would like to find the ratio of How to calculate the ratio of SUM of Total Employee divide by the Sum of Total Plan by each department. Can i know what should i type inside the Powerbi new column using DAX.

I have shared the excel spreadsheet on google drive. (H and I column are the result i am looking for 

https://docs.google.com/spreadsheets/d/1cPOLgBYqttGg2wH-MYQli3mt2p5NiWgE/edit?usp=sharing&ouid=10579...

This is what i have added in my Powerbi column code and it doesnt work

Ratio on Total Employees/Employees by Department (Plan) = TargetEmployees[Total Plan] / SUM (TargetEmployees[Total Plan]), COUNT(Distinct [Department/Section])

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Can you try this code:

Total Plan ratio =
VAR _total =
    SUMX( ALL( 'Table' ), [Total Plan] )
VAR _subtotal =
    SUM( 'Table'[Total Plan] )
RETURN
    DIVIDE( _total, _subtotal )

result:

I had not created any columns.

ALL( ) is used  to remove all filters on the table. SUMX() and SUM() function you can refer here.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your code. It works perfectly. 😀

[Anonymous]

The issue i faced is that Department type is text and i am not unable to use numbers to divide by text. Any help is appreciated. Thanks!

[amitchandak]

@Anonymous , measure which can think of this

Plan M= calculate( sum(TargetEmployees[Total Plan] ), allselected())

Future M= calculate( sum(TargetEmployees[Future Plan] ), allselected())

Future M= calculate( distinctcount(Table[Department/Section]) , allselected())

These will give you total you needed

[Anonymous]

Hello amitchandak, i have tried your method and it doesnt work. 

No	Department Code	Department/Section	Designation	Total Plan	Future Plan	Difference
1	Administration1	General	General Manager	7	7	0
2	Administration2	General	Assistant  Manager	0	0	0
3	Administration3	General	Senior Manager	0	0	0
4	Finance1	Finance	Finance Manager	1	1	0
5	Finance2	Finance	Assistant Financial Manager	2	2	0
6	Finance3	Accounting	Accounting Manager	0	0	0
7	Finance4	Accounting	Assistant Accounting Manager	1	1	0
8	Finance4	Accounting	Accounting Supervisor	1	0	-1
9	Production1	Sewing	Sewing Technician Supervisor	0	0	0
10	Production2	Sewing	Sewing Technician Leader	1	1	0
11	Production2	Sewing	Sewing Technician	1	0	-1
12	Production2	Sewing	Sewing  Admin	3	0	-3
13	Production3	Sewing	Sewing  Admin	1	0	-1
14	Production4	Finishing	Finishing QC Supervisor	7	0	-7
15	Production4	Finishing	Finishing QC Leader	54	0	-54
16	Production4	Finishing	Operator	12	10	2
17	Production4	Finishing	Operator	12	10	2
18	Production4	Finishing	Operator	45	2	43
19	Production4	Finishing	Operator	56	30	26
20	Production4	Finishing	Operator	1	10	-9

If you look carefully at the excel file i have provided. I want column H and I to sum (Total Plan) / Total Plan based on their department level. For eg. General. The total plan value is 205, and my SUM of department (General) is 7. The ratio i will be getting is 29.28.

Another example, Finishing department. The total plan value is 205, and my department(Finishing) is 187. The ratio i will be geting is 205/187 = 10.9

How to get 187?  (7 + 54 + 12 + 12 + 45 + 56 + 1) 

[amitchandak]

@Anonymous , Check if the attached file can help

[Anonymous]

I understand your method of working. Seems like it is something i am looking for.

i have encountered some error when i calculate using my powerbi file. I copied exactly the code you have provided for me. Any advise?

This is my code: 

Sub Total = CALCULATE(SUM(TargetEmployees[Total Plan]), filter(ALLSELECTED(TargetEmployees), [Department/Section] =max([Department/Section])))

[Anonymous]

Can you kindly explain what is your calculated measures are doing as well as the need to create a new column in the data tab. Thanks.