DAX measure to calculate MAX for a given group

[saglamtimur]

Hi,

I have data like

I need a measure to calculate MaxItemPrice. I have lots of filters which are not show, and grouping may be changed (eg:CustomerName->ItemName->StateName or StateName->Customername->ItemName). How can I achieve this? Thanks.

[v-chuncz-msft]

@saglamtimur,

It seems that you just need to use the Matrix visual.

[saglamtimur]

I have already, but still problem exists.

[v-chuncz-msft]

@saglamtimur,

The simple MAX Function seems to be working. Show us the expected result more clearly.

[saglamtimur]

What I would like to achieve

[DAX0110]

If I understand you correctly, you want to compute the maximum price for an item across all customers in all states, regardless of other filters that are in place?

You can try this measure: (assuming the name of the table is ItemPrices)

=CALCULATE(

             IF( HASONEVALUE(ItemPrices[ItemName])

                 , MAX(ItemPrices[ItemPrice])

                 , BLANK()

                 )

           , ALLEXCEPT( ItemPrices, ItemPrices[ItemName] )

           )

This formula will return blank for subtotal and grand total cells, unless that total pertains to exactly one item.

[saglamtimur]

Thanks for your quick reply.

All filters should be applied, like Year, Month, Country etc...

This works in Excel, users can change the order of grouping as I mentioned.

Subtotals and grand total are not important for me. I left them blank just to separate groups.

I believe, I cannot apply filters properly. Simple MAX(ItemPrices[ItemPrice]) works for each given ItemName, but the shown value is max of all without applying filters.