Measure that sums based on the values of certain columns

Anonymous — Tue, 21 Jun 2022 21:29:34 GMT

Hi,

Sorry if I put this at the wrong place, or something is unclear, I'm a newbie.

I also couldn't find a similar problem unfortunately.

Problem explanation:

I am trying to write a measure that will return the sum of certain values based on two columns.

Below you can see how the data is formatted. In this dataset there are two (but could later be more) unique references, I want to sum the cost of the highest recommendations (based on the number in that cell: "recommendation 3:"). So it would take just one value per reference to sum. In this case the measure should sum the 1000 and the 1500 since they respectively have the highest recommendation number of this dataset.

I tried adding a calculated column with just the recommendation number and then running the calculation with that. But I couldn't figure out how to make a measure that sum's based on these two conditions. (I did this within the power query by copying that column to create a new column and slicing it so i only keep the number). Unfortunately also with this I couldn't make it to work.

Curious if this is possible and how I could approach this best, I am looking forward to your feedback!

Thanks a lot in advance and let me know if something is unclear 😉

Some sample data to visualize it:

Reference	Recommendations	cost
1404BC_20_	recommendation 1: lorem itsem	500
1404BC_20_	recommendation 2: lorem itsem	850
1404BC_20_	recommendation 3: lorem itsem	1000
6308EC_24_	recommendation 1: lorem itsem	400
6308EC_24_	recommendation 2: lorem itsem	900
6308EC_24_	recommendation 3: lorem itsem	1100
6308EC_24_	recommendation 4: lorem itsem	1500

Re: Measure that sums based on the values of certain columns

vapid128 — Tue, 21 Jun 2022 23:15:55 GMT

sumx(values([Reference),calculate(max[cost)) = 1500+1000=2500

Re: Measure that sums based on the values of certain columns

Anonymous — Wed, 22 Jun 2022 07:52:32 GMT

That was easier than I expected. Thanks a lot!

topic Measure that sums based on the values of certain columns in DAX Commands and Tips

Measure that sums based on the values of certain columns

Re: Measure that sums based on the values of certain columns

Re: Measure that sums based on the values of certain columns