Forum Discussion

TTT666's avatar
TTT666
New Member
5 years ago
Solved

max and min value

Hello all,

I need to write something in DAX, but I can't figure out how to get the context right. Hopefully, you can help me.

I have a fact table with a set of sales from different categories and dates (see first image to see an example for Category A). I need to create a table visualization in Power BI like Image 3, that is to say a table that displays mothly sales by category (Image 2) and add two columns that displays the minimum and maximum amount of monthly sales by each category. Not from one month, but from all months together. Down below, in Image 3, I marked in green the columns that I would like to add but I can´t get the context rigth.

 

Thanks a lot

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi TTT666 ,

     

    Sorry for my mistake. This is the modified measure.

    Measure = 
SWITCH (
    MAX ( 'Table (2)'[Type] ),
    "Min(Sales)",
        MINX (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Month], 'Table'[Category] )
            )
        ),
    "Max(Sales)",
        MAXX (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Month], 'Table'[Category] )
            )
        ),
    "Total", SUM ( 'Table'[Sales] )
)

     

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi TTT666 ,

     

    My apologies for the delayed response. Below is my solution.

     

    1.Create a separate table by entering data. 

     

    2.Create a measure.

    Measure = SWITCH(MAX('Table (2)'[Type]),"Total",SUM('Table'[Sales]),"Min(Sales)",MIN('Table'[Sales]),"Max(Sales)",MAX('Table'[Sales]))

     

    3.Create a visual as follows.

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

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

    • TTT666's avatar
      TTT666
      New Member
      5 years ago

      Thanks a lot Anonymous , but it's not exactly what I need. I need the max value for Category A would be always the same (for all months). The max value for Category B would be always the same (for all months) and the max value for Category C would be always the same (for all months). The max value for A would be the biggest monthly sales for A and so on. The same for min value (the lowest monthly sales).

       - Monthy sales= sum(sales) for each month

      You can see it (in green) in my Image 3. 

      Using your example (thanks for it) I would need this results:

      Category A --> max value=190 for all months, min value=50 for all months 

      Category B -->                     240                                          50

      Category C-->                      170                                          50

       

      I think the solution is to create a table SUMMARIZE first. Do you think is the best solution?

      Thanks a lot

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi TTT666 ,

         

        Sorry for my mistake. This is the modified measure.

        Measure = 
SWITCH (
    MAX ( 'Table (2)'[Type] ),
    "Min(Sales)",
        MINX (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Month], 'Table'[Category] )
            )
        ),
    "Max(Sales)",
        MAXX (
            ALLEXCEPT ( 'Table', 'Table'[Category] ),
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                ALLEXCEPT ( 'Table', 'Table'[Date].[Month], 'Table'[Category] )
            )
        ),
    "Total", SUM ( 'Table'[Sales] )
)

         

         

        You can check more details from here.

         

         

        Best Regards,

        Stephen Tao

         

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

    • TTT666's avatar
      TTT666
      New Member
      5 years ago

      Thanks for your response Ashish_Mathur but I think you didn´t undertand me or maybe I didn´t explained properly my problem. My source data is the table in Image 1, this is the only source data I have, and I displayed an example (some rows). Additionally I displayed Image 2 and 3 to explain the final visual in Power BI I want to. I displayed it in Excel in order to show the result, but they aren´t source data. I wanted to know how to get a visual like Image 3 from a data table like Image 1. Now I have an idea, I need to use SUMMARIZE function.

      Thank you anyway

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Try this approach:

        1. Create a Calendar Table and write calculated column formulas to get the Year, Month name and month number.  Sort the Month name by Month number
        2. Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.
        3. To your matrix visual, drag Year and Month name columns to the Column section and Category column to the row section
        4. Write these measures

        Total sales = SUM(Data[Sales])

        Max sales = MAXX(ALL(Calendar[Month name]),[Total sales])

        Min sales = MINX(ALL(Calendar[Month name]),[Total sales])

        Hope this helps.