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,
Iam stucked into get cumluative/running total of an table with multiple group columns. Table looks like this:
Category 1 | Category 2 | Account | Date | Amount |
Cat1-Number1 | Cat2-Number1 | 3010 | 2018-07 | 10 |
Cat1-Number1 | Cat2-Number1 | 3010 | 2018-08 | 5 |
Cat1-Number1 | Cat2-Number1 | 3010 | 2018-09 | 50 |
Cat1-Number1 | Cat2-Number1 | 3010 | 2018-10 | 90 |
Cat1-Number1 | Cat2-Number2 | 3011 | 2018-07 | 90 |
Cat1-Number1 | Cat2-Number2 | 3011 | 2018-08 | 100 |
Cat1-Number2 | Cat2-Number1 | 3012 | 2018-08 | 100 |
Then i want running total should be:
Cat1-Number1 => Cat2-Number1 => 3010 => 155
Cat1-Number1 => Cat2-Number2 => 3011 => 190
Cat1-Number2 => Cat2-Number1 => 3012 => 100
I have try this formula but that want be correct:
=CALCULATE(SUM('Table'[Amount]), FILTER(ALLEXCEPT('Table', 'Table'[Account]), 'Table'[Date] >= EARLIER('Table'[Date])))
Thats had work if o only got Account as group but now i have Category 1 and Category 2 also.
So how should i think to resolve this one?
Solved! Go to Solution.
I figure it out. This is the correct formula:
=CALCULATE(SUM('Table'[Amount]), FILTER(ALLEXCEPT('Table', 'Table'[Account], 'Table'[Category1], 'Table'[Category2]), 'Table'[Date] <= EARLIER('Table'[Date])))
I figure it out. This is the correct formula:
=CALCULATE(SUM('Table'[Amount]), FILTER(ALLEXCEPT('Table', 'Table'[Account], 'Table'[Category1], 'Table'[Category2]), 'Table'[Date] <= EARLIER('Table'[Date])))
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |