Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am looking for a way to summarise a column based on a change in a different column's value. So, for example, I would like to take the raw data below:
id | category | value |
1 | a | 20 |
1 | b | 20 |
1 | c | 20 |
1 | d | 20 |
1 | e | 20 |
1 | f | 20 |
2 | a | 35 |
2 | c | 35 |
2 | d | 35 |
2 | f | 35 |
3 | c | 5 |
3 | d | 5 |
3 | g | 5 |
3 | h | 5 |
3 | a | 5 |
3 | f | 5 |
I want to be able summarise the column value based on the change in the column id. So, if I created a table that included the id column then it would look like this:
id | value |
1 | 20 |
2 | 35 |
3 | 5 |
but also to have the flexibility to remove the id column and summarise as a total like this:
total |
60 |
I have tried a workaround by using the AVERAGE measure, which works for the first summary table, but fails on the second (it averages out at 18.125 rwhere I want to see the running total of value based on change of id column value.
Also, the solution I would need would ideally allow me to filter on the category column.
I get a feeling that the solution lies somewehere around creatinga column/measure using the DAX function CALCULATE, but I have tried a couple of suggested fixes to similar issues and can't for the life of me get this to work.
Any help much appreciated! Thanks.
Hi,
_cal = SUMX(VALUES('Table'[id]), CALCULATE(MIN('Table'[value])))
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |