The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello!
I have a dataset that is very extensive with many categories (and some calculated measures), I would like to create a measure or a data table that will create mins and maxes of each category by week, over a set time period (say last 3 years). I have the data mapped to a date table and use it to create "seasonality" graphs that overlays data, each year being a line, and a different chart for each category.
Then I would like to chart the most recent data as a line on a graph and overlay that with last 3 years average appearing as a shaded region on the graph. I think I can do this once I have a table of values. However, I calculate that in order to do this for my current dataset it would be 84 columns. Is there an easy way to do this or am I forced to brute force it? And if I have to brute force it, how can I use a measure in a column of a power query?
Below is a sample data set to help the conversation.
Thank you for your help!
Category | Week of Year | Year | Value |
Category 1 | 1 | 2022 | 5 |
Category 2 | 1 | 2022 | 50 |
Category 1 | 2 | 2022 | 1 |
Category 2 | 2 | 2022 | 300 |
Category 1 | 1 | 2021 | 2 |
Category 2 | 1 | 2021 | 400 |
Category 1 | 2 | 2021 | 8 |
Category 2 | 2 | 2021 | 100 |
Category 1 | 1 | 2020 | 7 |
Category 2 | 1 | 2020 | 200 |
Category 1 | 2 | 2020 | 3 |
Category 2 | 2 | 2020 | 200 |
Goal | Category 1 Min | Category 1 Max | Category 2 Min | Category 2 Max |
Week1 | 2 | 7 | 100 | 300 |
Week2 | 1 | 8 | 50 | 400 |
Thank you for the response.
I am getting the desired result when the page filters are wider than this year - 5 years. For some reason the page filters are still effecting the data, even when I utilize the all function to remove any filters from the data set and do all the filtering in the measure.
And yes I do have a date table that is separate from where my data lives, however I was just trying to mess around with different filters, neither filtering with the date filter nor with the data table works.
Thanks!
I also tried to use the page filters to my advantage and use the allexcept function to remove all filters except those on the map descriptors table, and that solution was giving me the same desired result when greater than 5 years was selected, but not when only this year was selected.
thanks,
Hi, @MatthewJM
You can try the following methods.
Max = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Category],'Table'[Week of Year]))
Min = CALCULATE(MIN('Table'[Value]),ALLEXCEPT('Table','Table'[Category],'Table'[Week of Year]))
Is this the result you expect? If not, please provide your desired outcome.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I would like the result to look like this. With the current data set being the only line on the graph, and use error bands to shade the region.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
93 | |
88 | |
70 | |
65 |
User | Count |
---|---|
232 | |
128 | |
116 | |
82 | |
82 |