Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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!
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.
@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!
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
@Anonymous
Formulas revised above!
That works! Thanks a ton!!! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |