Measure for max of a group where the total is the sum of the unique max value for each group

Anonymous — Tue, 07 May 2019 12:13:44 GMT

I have this data set with some weekly data.

For the alllocated FFE the value for each week is repeated for a given ID

I want a measure that shows me the sum value for each week, but the total for the same measure should only be the sum of the distinct values across all the weeks for each ID.

So the value for week 201902 would be 400 without the ID in the table as two IDs has values for week 2

And the total should be=400 (220+180) as it should be the total for each ID's distinct value

The formula I have right now is:

IF(HASONEFILTER('Allocation Info'[Allocation YearWeek]);

SUMX(FILTER('Allocation Info';'Allocation Info'[R/I]=0);'Allocation Info'[FFE_Src]);

SUMX(VALUES('Allocation Info'[Allocation YearWeek]);CALCULATE(MAX('Allocation Info'[FFE_Src]);FILTER('Allocation Info';'Allocation Info'[R/I]=0)))

)

But this part isn't really working:

SUMX(VALUES('Allocation Info'[Allocation YearWeek]);CALCULATE(MAX('Allocation Info'[FFE_Src]);FILTER('Allocation Info';'Allocation Info'[R/I]=0)))

As I am getting the sum of all the records...

This measure should also work without the ID present in the table (so just the weeks and the measure)

It seems like the IF(HASONEFILTER('Allocation Info'[Allocation YearWeek]); part is not really working

Re: Measure for max of a group where the total is the sum of the unique max value for each group

Anonymous — Tue, 07 May 2019 12:29:49 GMT

Turns out the solution was alot more simple:

MAXX(VALUES('Allocation Info'[Allocation YearWeek]); [FFE])

Where [FFE] is just a notmal SUM of 'Allocation Info'[FFE_Src]

topic Measure for max of a group where the total is the sum of the unique max value for each group in DAX Commands and Tips

Measure for max of a group where the total is the sum of the unique max value for each group

Re: Measure for max of a group where the total is the sum of the unique max value for each group