cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ondrap
New Member

Visual that shows the top performing month in a year grouped by year

I am having trouble with the Top 1 filter for a visual that is supposed to show the top performing month by profit and group the collumns by year.

 

For some reason it will take the top performing month of year 2013 and then the same month from year 2014 (the top performing month for 2014 is December)

ondrap_2-1669661541808.png

 

ondrap_1-1669661430081.png

 

I don't understand what I am doing wrong.

 

 

 

 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

You can use a DAX measure like this one to get that result. This find the max for all months in the given year and returns blank for all but the max month (and are filtered out in the visual). The visual uses the yearmonth column from my Date table and this measure on the Y axis.

 

Max Month Only =
VAR vThisResult = [Total Qty]
VAR vThisYear =
    MAX ( 'Date'[Year] )
VAR vMaxThisYear =
    CALCULATE (
        MAXX ( VALUES ( 'Date'[YearMonthShort] ), [Total Qty] ),
        ALL ( 'Date' ),
        'Date'[Year] = vThisYear
    )
RETURN
    IF ( vThisResult = vMaxThisYear, vThisResult, BLANK () )

 

ppm1_0-1669681387083.png

 

Pat

 

 

Microsoft Employee

View solution in original post

1 REPLY 1
ppm1
Solution Sage
Solution Sage

You can use a DAX measure like this one to get that result. This find the max for all months in the given year and returns blank for all but the max month (and are filtered out in the visual). The visual uses the yearmonth column from my Date table and this measure on the Y axis.

 

Max Month Only =
VAR vThisResult = [Total Qty]
VAR vThisYear =
    MAX ( 'Date'[Year] )
VAR vMaxThisYear =
    CALCULATE (
        MAXX ( VALUES ( 'Date'[YearMonthShort] ), [Total Qty] ),
        ALL ( 'Date' ),
        'Date'[Year] = vThisYear
    )
RETURN
    IF ( vThisResult = vMaxThisYear, vThisResult, BLANK () )

 

ppm1_0-1669681387083.png

 

Pat

 

 

Microsoft Employee

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.