Data label only for Max Value with Time Aggregation parameter

[Pk8524]

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 · Accepted solution]

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]

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

[johnt75]

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.

[Pk8524]

why it is needed to remove filters on quarter nr?

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