RANK highest month's sales by quarter

[speedramps]

See the attached PBIX

Click here for PBIX 

The DimDate table has a one to many relationship with FactSales.

The DimDate table has a  separate year, quarter and month column which I want to use rather than the default date hierarchy.

The report has 3 visuals:-

• Shows sales ranked by year.
• Shows sales ranked by year and quarter.
• Shows sales ranked by year, quarter and month

Each report uses DAX measure

I need a little DAX help please with report 3) to rank the month with the highest sales for each quarter by year

For example:-

• March should rank highest in 2018 Q1
• May should rank highest in 2018 Q2
• September should rank highest in 2018 Q3

Thank you

[v-angzheng-msft · Accepted solution]

Hi @speedramps 

I try to modify the DAX expression you wrote, I just replace DimDate[MonthLongName] with DimDate[MonthNum]

What’s yours:

Rank by year quarter month =
RANKX ( ALL ( DimDate[MonthLongName] ), [Total quantity] )

New:

Rank by year quarter month （2） =
RANKX ( ALL ( DimDate[MonthNum] ), [Total quantity] )

It is important to note that you must remove the DimDate[MonthLongName] column,

and replace it with DimDate[MonthNum] in the table visual,

otherwise you will not get the correct results.

Sample data:

Result:

Is this the result you want? Hope this is useful to you

Please feel free to let me know if I misunderstand your question

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sean]

Hello @speedramps  and @v-angzheng-msft 

This unexpected behavior is a "side effect" of using the Sort by Column feature!

When a DAX expression removes a filter from a column that has the Sort By Column setting active,

it is a best practice to always include both columns in the ALL  function.

Read more about it here

What this simply means is that your measure will work with the [MonthLongName] column but because you've sorted it by the [MonthNum] Column you have to include both columns in the ALL function just like this: 

Rank by year quarter month =
RANKX (
    ALL (
        DimDate[MonthNum],
        DimDate[MonthLongName]
    ),
    [Total quantity]
)

Hope this helps! 😃

Sean

[speedramps]

Thank you
It is still not working.
I want to rank months byhighest sales each quarter  

Click here for PBIX version 2

See PBIX example 2 

[amitchandak]

@speedramps , You need to try measures like

Year Rank = Rankx(Allselected('Date'[Year]), [Sales],,desc,dense)

Qtr Year Rank = Rankx(Allselected('Date'[Qtr Year]), [Sales],,desc,dense)

Qtr in Year Rank = Rankx(filter(Allselected('Date'[Qtr Year], 'Date'[Year]),'Date'[Year] =max('Date'[Year])), [Sales],,desc,dense)

Month, in Qtr in Year Rank = Rankx(filter(Allselected('Date'[Month Year],'Date'[Qtr Year], 'Date'[Year]),'Date'[Year] =max('Date'[Year])
&& 'Date'[Qtr Year] =max('Date'[Qtr Year])), [Sales],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[speedramps]

Thank you
It is still not working.
I want to rank months byhighest sales each quarter  

Click here for PBIX version 2

See PBIX example 2