Calculated minimum value by multiple categories

[kavitabehera]

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 @Power5 

[v-jialluo-msft · Accepted solution]

^{Hi @kavitabehera ,}

^{I think it is just necessary to add another calculation table on top of the example file
(1) Add a calculation table}

Table 2 = SUMMARIZE (
    'Table', 'Table'[Region],
    "MIN",MIN('Table'[MinXSale])
) 

^{(2)Final output}

^{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.}

[v-jialluo-msft]

^{Hi @kavitabehera ,}

^{I tried to open the file link, but it says permission is required.}

^{Please upload the sample file directly to the attachment or remove the access restriction from the download link}

^{Best Regards,}

^{Gallen Luo}

[kavitabehera]

Hi @v-jialluo-msft 

You can access the file now.

Here is the link.

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

[v-jialluo-msft]

^{Hi @kavitabehera ,}

^{Please follow these steps:}

^{(1) Create a new calculated table}

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

^{(2) 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.}

[kavitabehera]

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

[Greg_Deckler]

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

[kavitabehera]

@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