Forum Discussion

kavitabehera's avatar
kavitabehera
Advocate II
3 years ago
Solved

Calculated minimum value by multiple categories

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 cat...
  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
kavitabehera
Advocate II
3 years ago

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