topic Re: Calculated minimum value by multiple categories in DAX Commands and Tips

Calculated minimum value by multiple categories

kavitabehera — Wed, 09 Nov 2022 16:38:18 GMT

I wanted to create a calculated table that fetches the minimum value by 2 text columns.

For example: Let's take this below example, the question is to bring the minimum sale for the region by category and segment.

The expected answer should be

Central	25482.34
East	37786.16

How to generate the correct DAX?

Thanks in advance!

Note I am trying to create it as a calculated table

Tagging a few pro members for support Anonymous Bibi Anonymous

Re: Calculated minimum value by multiple categories

Greg_Deckler — Wed, 09 Nov 2022 16:48:46 GMT

kavitabehera 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.

Re: Calculated minimum value by multiple categories

v-jialluo-msft — Thu, 10 Nov 2022 07:09:02 GMT

^{Hi kavitabehera ,}

^{Please follow these steps:}

⁽¹⁾ ^{Create a new calculated table}

Table 2 = SUMMARIZE ( 'Table', 'Table'[Region], "Min", MIN ( 'Table'[Total Sale] ) )

⁽²⁾ ^{Final output}

^{According to known data, the minimum value of EAST should be 29870.36,.}

^{if the above resulte is not what you need or I do not understand your need, please indicate the correct approach or provide specific data}

^{Best Regards,}

^{Gallen Luo}

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

Re: Calculated minimum value by multiple categories

kavitabehera — Fri, 11 Nov 2022 13:23:29 GMT

Thank you for replying.

In your case, it worked because we don't have any other columns except these three. But let's assume we have one more column "colors" due to which the minimum value will affect.

Currently, with this measure, I can summarize region and category by min sales by segment. Here is the snapshot

Table = SUMMARIZECOLUMNS ( Orders[Region], Orders[Category], "MinXSale", MINX ( VALUES ( Orders[Segment] ), [Total Sale] ) ) --Minimum Sale by Segment

But the answer I am looking for is

Central

25482

East

29870

West

33638

South

16853

Here is the dataset link that will help you recreate the situation.

https://drive.google.com/file/d/1xGwidMFLevCFmp9OQHjSvlj-skPZEATn/view?usp=sharing

Re: Calculated minimum value by multiple categories

kavitabehera — Thu, 10 Nov 2022 10:05:24 GMT

Greg_Deckler

Thank you for your response.

The case I am trying to achieve is when I want "Region minimum sale by 2 categories (Category & Segment)". Through the above DAX I can add and create something like "Region & Category minimum sale by Segement."

The DAX I used

Table = SUMMARIZECOLUMNS (

Orders[Region],

Orders[Category],

"MinXSale", MINX ( VALUES ( Orders[Segment] ), [Total Sale] )

) --Minimum Sale by Segement

Result

This is not what I want, I only want the region and its highlighted values.

Here is the link to the dummy PBIX file.

Power BI File