Forum Discussion
How to plot maximum/minimum value plot for selected item's category
Table 1:
Date | Item | Sales Amount |
10/01/2021 | Apple | 300 |
10/02/2022 | Orange | 700 |
20/03/2022 | Carrot | 300 |
21/03/2022 | Onion | 800 |
Table 2:
Date | Item | Category | Sales Amount |
10/01/2021 | Apple | Fruits | 300 |
20/2/2022 | Carrot | Vegetable | 800 |
10/02/2022 | Orange | Fruits | 700 |
21/03/2022 | Onion | Vegetable | 200 |
Relationship: Table1[Item] and Table2[Item] are connect using Many to many relationship.
Im trying to plot three lines of graph for table 1.
Line 1- Represents the maximum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)
Line 2 – Represents the sale amount of selected item over the date.
Line 3 -- Represents the minimum sale amount based on its category(category value retrieved from selected value of item displayed in slicer)
My Problem is
Unable to plot the maximum/minimum sale amount line based on the slicer item category.
When you change the item in the slicer, the maximum/minimum line chart must be updated based on the selected item category, but in this case, the lines change based on the selected item value rather than the max/min value.
My Question is:
How to plot the maximum/minimum value chart only based selected item’s category.
Example :
If I choose the apple in slicer, the maximum/minimum plot will show the maximum and minimum value of the fruits category.
amitchandak could you please look into this
Try the following change:
Maximum = VAR CategorySelection = VALUES( TableDimension[Category] ) RETURN CALCULATE ( MAX ( Table[Sales] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection ) )Hi Maggi029 ,
Try to change the metric of the max/min to somwething similar to this:
Maximum = VAR CategorySelection = VALUES( TableDimension[Category] ) var ItemValues = CALCULATE ( DISTINCTCOUNT ( Table[Item] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection ) ) RETURN IF(ItemValues > 1, CALCULATE ( MAX ( Table[Sales] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection ) ) )This should return blank for the maximum and minimum when there is only a single value so the labels will not appear.
15 Replies
Hi Maggi029 ,
I believe that you need to create two dimension tables onbe for dates and another for the items to make your relationships between the tables.
However I have one question you have sales on both tables what is the sales column you want to see on the chart? Is it the values from table1, table 2 or both?
Thanks for your response,
I have made the releationship as suggested, but the real problem here how to wrote measure to plote the max/min for selected Item Category.
For your question:
It is just a replicate of original data, so table 2 has separate reports in my projectHi Maggi029 ,
If the values are the same and you create a dimension table with the items/Category then you can create a similar metric to have the maximum or minimum:
Maximum = VAR CategorySelection = SELECTEDVALUE ( TableDimension[Category] ) RETURN CALCULATE ( MAX ( Table[Sales] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] = CategorySelection ) )Be aware that I'm doing this by heart.
MFelix
I need your suggestion for below query , which is also related to this same data table
How to find the maximum value of a measure in tabl... - Microsoft Power BI Community
