Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Hi,
Try this approach:
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |