Forum Discussion

Pk8524's avatar
Pk8524
Helper I
1 year ago
Solved

Data label only for Max Value with Time Aggregation parameter

Hi Guys,

 

How to show only one data label with Max value for certain Time aggregation parameter?

 

 

I am using following parameter:

 

Time Aggregation = {
    ("Year", NAMEOF('dimCalendar'[Year]), 0),
    ("Quarter", NAMEOF('dimCalendar'[Quarter]), 1),
    ("Month", NAMEOF('dimCalendar'[Month]), 2)
}

 

Link to file:

https://drive.google.com/file/d/1zX0aBAOcYPi3IIF0-N2Mh-0_j-f-as3D/view?usp=sharing 

  • johnt75's avatar
    johnt75
    1 year ago

    You can create a new measure

    Total Sales Data Label = 
VAR CurrentValue = [Total Sales]
VAR MaxValue = SWITCH(
    SELECTEDVALUE( 'Time Aggregation'[Time Aggregation Order] ),
    0, -- Year 
    MAXX(
        ALLSELECTED( dimCalendar[Year] ),
        [Total Sales]
    ),
    1, -- Quarter 
    MAXX(
        ALLSELECTED( dimCalendar[Quarter] ),
        CALCULATE( [Total Sales], REMOVEFILTERS( dimCalendar[QuarterNr] ) )
    ),
    2, -- Month 
    MAXX(
        ALLSELECTED( dimCalendar[Month] ),
        CALCULATE( [Total Sales], REMOVEFILTERS( dimCalendar[MonthNr] ) )
    )
)
VAR Result = IF( CurrentValue = MaxValue, CurrentValue )
RETURN Result

    and use that as the value for the data labels on the chart visual.

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can create a new measure

    Total Sales Data Label = 
VAR CurrentValue = [Total Sales]
VAR MaxValue = SWITCH(
    SELECTEDVALUE( 'Time Aggregation'[Time Aggregation Order] ),
    0, -- Year 
    MAXX(
        ALLSELECTED( dimCalendar[Year] ),
        [Total Sales]
    ),
    1, -- Quarter 
    MAXX(
        ALLSELECTED( dimCalendar[Quarter] ),
        CALCULATE( [Total Sales], REMOVEFILTERS( dimCalendar[QuarterNr] ) )
    ),
    2, -- Month 
    MAXX(
        ALLSELECTED( dimCalendar[Month] ),
        CALCULATE( [Total Sales], REMOVEFILTERS( dimCalendar[MonthNr] ) )
    )
)
VAR Result = IF( CurrentValue = MaxValue, CurrentValue )
RETURN Result

    and use that as the value for the data labels on the chart visual.

    • Pk8524's avatar
      Pk8524
      Helper I
      1 year ago

      why it is needed to remove filters on quarter nr?

       

      MAXX(
        ALLSELECTED( dimCalendar[Quarter] ),
        CALCULATE( [Total Sales], REMOVEFILTERS( dimCalendar[QuarterNr] ) )
    ),

       

    • Pk8524's avatar
      Pk8524
      Helper I
      1 year ago

      why it is needed to remove filters on quarternr in case of quarter?

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        That column is used to sort the Quarter column, same for Month number and month. Because they are used for sorting they are included in the underlying query generated for the visual, and are therefore in the filter context when the measure is calculated for a given month / quarter. We need to remove those filters so that we get the right total.