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 category and segment.

The expected answer should be

Central25482.34
East37786.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 

 

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

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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

      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 

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support

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

      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 

      Central25482

      East

      29870West33638South16853

       

       

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

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

       

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support

    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's avatar
    v-jialluo-msft
    Community Support

    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