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.
Hi,
I have difficulties to calculate ratio including a sum with a subtotal
Based on the example below (table "customer") I need to create a matrix table with Path and x/y as rows and calulated results in columns
Path | x/y | Step | Nb Cus | Inc | Color |
A | x | 1 | 34 | Blue | |
A | y | 1 | 91 | Black | |
B | x | 2 | 83 | Red | |
B | y | 2 | 61 | Green | |
C | x | 2 | 75 | White | |
C | y | 2 | 71 | Purple | |
D | x | 2 | 99 | Black | |
D | y | 2 | 84 | Red | |
A | x | 1 | 66 | Blue | |
A | y | 1 | 32 | Black | |
B | x | 2 | 12 | Red | |
B | y | 2 | 22 | Green | |
C | x | 2 | 79 | White | |
C | y | 2 | 97 | Purple | |
D | x | 2 | 33 | Black | |
D | y | 2 | 26 | Red |
First and seconds columns are the sum for number of customer and income. No problem here
For the other calculations, I need to divide the total (sum Inc) by the sum NbCus when step = 1. For now I only have results where path equals A but I need to use the results (66+32) for other Path as well
I will also need to be able to filters(by color and other variables available) in my report and this matrix
I think I need to use the allexcept function but have difficulties to understand how. Any suggestion ?
Thanks for the answer but this solution does not select the sum(Nb cus) where step = 1 and apply this results as divider for any Path, x/y and step combination. The provided answer only works for the first 2 rows but not after. For example for the third row, I need to be able to create the formula 83/(66+32). Did I miss something?
Works fine with 2 measures:
ZZM_SumNBCust = CALCULATE(SUM(data[Nb Cus]), ALL(data), data[Step] = 1")
ZZM_SumIncDivided = SUM(data[Inc]) / [ZZM_SumNBCust]
@Anonymous ,
Just try to add a measure.
Measure = DIVIDE ( SUM ( Table1[Inc] ), SUM ( Table1[Nb Cus] ) )
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |