Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Friends,
I understand this is not a Power Query forum but I want to understand first how to calculate the percentage of a grand total in my data set using both Power Query and Power BI.
I have provided an example below.
Description | No of Units | Units Sold | Percentage of units Sold | |
James | 286 | 252 | 88% | Created a custom calculated column and the values are coming correctly |
Wendy | 253 | 177 | 70% | |
Charlie | 90 | 51 | 57% | |
Kim | 11 | 4 | 36% | |
Ben | 2 | 1 | 50% | |
Sara | 1 | 0 | 0% | |
Grand Total | 643 | 485 | 75% (485/643) | This is not being calculated correctly in Power BI, it shows either 100% or sum of the values. |
Solved! Go to Solution.
I'm not sure what you mean by creating the calculation in Power Query but this would be simple in DAX
Pct Sold = DIVIDE ( SUM ( 'Table'[Units Sold] ), SUM ( 'Table'[No of Units] ) )
Or if you really want it in PQ, you can add a custom column with this formula
=[Units Sold]/[No of Units]
Or if you want to sum and divide the two columns
List.Sum(#"Changed Type"[Units Sold])/List.Sum(#"Changed Type"[No of Units])
What this will do is it will divide the sum of Units Sold column by sum of No of Units Column. In this case, the added column will have the same value for each row.
Proud to be a Super User!
I'm not sure what you mean by creating the calculation in Power Query but this would be simple in DAX
Pct Sold = DIVIDE ( SUM ( 'Table'[Units Sold] ), SUM ( 'Table'[No of Units] ) )
Or if you really want it in PQ, you can add a custom column with this formula
=[Units Sold]/[No of Units]
Or if you want to sum and divide the two columns
List.Sum(#"Changed Type"[Units Sold])/List.Sum(#"Changed Type"[No of Units])
What this will do is it will divide the sum of Units Sold column by sum of No of Units Column. In this case, the added column will have the same value for each row.
Proud to be a Super User!
I guess summarization of percentages doesn't make sense.
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |