Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
robmarsh
Frequent Visitor

Conditionally summarise based on change in column value

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:

 

idcategory   value
1a20
1b20
1c20
1d20
1e20
1f20
2a35
2c35
2d35
2f35
3c5
3d5
3g5
3h5
3a5
3f5

 

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:

 

idvalue
120
235
35

 

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.

1 REPLY 1
stevedep
Memorable Member
Memorable Member

Hi,

_cal = SUMX(VALUES('Table'[id]), CALCULATE(MIN('Table'[value])))

sumx.png

Kind regards, Steve.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors