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.

MFelix's avatar
MFelix
Super User
4 years ago

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.

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.