Forum Discussion
How to plot maximum/minimum value plot for selected item's category
- 4 years ago
Try the following change:
Maximum = VAR CategorySelection = VALUES( TableDimension[Category] ) RETURN CALCULATE ( MAX ( Table[Sales] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection ) ) - 4 years ago
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.
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 project
- MFelix4 years agoSuper User
Hi 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.
- Maggi0294 years agoHelper II
Thanks for response, You measure is working fine, but I have one special case
Case : Because one of the items belongs to two categories, "SELECTEDVALUE" does not return the expected value because it has two values.
Can suggest how to handle above case?
- MFelix4 years agoSuper User
Try the following change:
Maximum = VAR CategorySelection = VALUES( TableDimension[Category] ) RETURN CALCULATE ( MAX ( Table[Sales] ), FILTER ( ALL ( TableDimension ), TableDimension[Category] in CategorySelection ) )