max and min value

[TTT666]

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

[Ashish_Mathur]

Hi,

Your source data is in a very poor format (especially Table2).  Not only is that Table missing dates, it also has multiple headings per column.  You will first have to get that second table in order to get your desires result.  See if my solution here helps to get the second table in order - Rearrange a multi heading dataset into a single heading one which is Pivot ready.

[TTT666]

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]

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.

[v-stephen-msft]

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]

Thanks a lot @v-stephen-msft , 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

[v-stephen-msft · Accepted solution]

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.