Help in create Max measure for aggregate value.

[aibikaidau]

I am struggling in creating a dax measure to calculate max unit quantity sold base on column in table visual.

I just create a simple measure "Max = Max(table[Total sold]) "and it seem ok in 1st visual but when I remove column restaurant_code it is not right anymore (picture below)

Please help. Thank you.

[v-jiewu-msft]

Hi @aibikaidau ,

Based on my testing, the max function is no problem. When drag the restaurant_code into the table visual, the Max measure will be maximized by a different code. So the result is shown in the first picture.

When removing the restaurant_code column, Total sold will automatically aggregated. So the Max measure will calculate the max value based on the day.

please try the following methods:

1.Create the sample table.

2.Create the measure to calculate MAX.

MeasureMax = MAXX(VALUES('Table'[restaurant_code]), CALCULATE(MAX('Table'[Total Sold])))

3.If remove the restaurant_code column from the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aibikaidau]

Hi,

What I want is the measure should show the max of accumulate quantities sold in each day. In your example, if you put Total Sold column in 3rd table, the max should show 109 (sum of 24-Mar-24).

Btw, thank you for your help.

[v-jiewu-msft · Accepted solution]

Hi @aibikaidau ,
try the following formula.

MeasureMax = CALCULATE(SUM('Table'[Total Sold]), ALLEXCEPT('Table','Table'[day]))

Drag the measuer into the table visual and the result is shown below.

If the above ones can’t help you get it working, could you please provide the expected result to make a deep troubleshooting? It would be helpful to find out the solution.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

What answer are you expecting in the second table?  Also, share the download link of the PBI file.

[Greg_Deckler]

@aibikaidau This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

[Zang_Mi]

Hello, it is normal that the measure returns different maximum values since the aggregattion has been done with different granularity levels. More information is needed to solve your problem, for example, a complete sample data for a specific date, with raw data, and the expected aggregation result with and without restaurant_code.

Regards