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