Forum Discussion

Maggi029's avatar
Maggi029
Helper II
4 years ago
Solved

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:   ...
General Comment
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    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 )
    )
  • MFelix's avatar
    MFelix
    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.

Maggi029's avatar
Maggi029
Helper II
4 years ago

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?

MFelix's avatar
MFelix
Super User
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 )
    )
  • Maggi029's avatar
    Maggi029
    Helper II
    4 years ago

    Thanks for for you help, your solution working fine. 

    Also i have one more query related to this, if a category has only one value, the Maximum and Minimum data points will be the same, making the group look crowded with the same value repeated when Data label is enabled. How can this be limited in this case by showing only one data label?

     

     

    • MFelix's avatar
      MFelix
      Super User
      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.