Forum Discussion

Pk8524's avatar
Pk8524
Icon for Helper I rankHelper 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('dimCale...
  • 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.

johnt75's avatar
johnt75
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper I
    1 year ago

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

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper 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.