Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX: Ranking a measure based on a Column

Hello All,   I am trying to create ranking of sales by customers and customer groups. Now these customers are been grouped as tier 1 and tier2.   But in visualization I am using only Customer co...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

     

    Total Sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Customer Group]=MAX('Table'[Customer Group])&&[Customer]=MAX('Table'[Customer])))
    Top 5 Customers = 
    RANKX(
        FILTER( ALL('Table'),[Tier1/2]=MAX('Table'[Tier1/2])),
        CALCULATE(
            [Total Sales])
            
            ,,
            DESC,Dense)

     

    The final show:

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

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous  Try if the two measures can help

 

M1= CALCULATE(SUM('Table'[Sales]),,filter(allseleced('Table'),'Table'[Customer] = max('Table'[Customer]) && 'Table'[Customer Group] =max('Table'[Customer Group] )))


Top 5 Customers =
RANKX(
ALL('Table'[Customer],'Table'[Customer Group], 'Table'[Date]),
[M1]
,,
DESC
)

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak below is the expected result that i am looking for.

Please help.

Begin of WeekCustomerTotal SalesTop 5Expected RankingRule
03-01-2022C18615105Part of Tier1 customegroup
10-01-2022C18615105Part of Tier1 customegroup
17-01-2022C18615105Part of Tier1 customegroup
24-01-2022C18615105Part of Tier1 customegroup
31-01-2022C18615105Part of Tier1 customegroup
03-01-2022C2991894Part of Tier1 customegroup
10-01-2022C2991894Part of Tier1 customegroup
17-01-2022C2991894Part of Tier1 customegroup
24-01-2022C2991894Part of Tier1 customegroup
31-01-2022C2991894Part of Tier1 customegroup
03-01-2022C51036283Part of Tier1 customegroup
10-01-2022C51036283Part of Tier1 customegroup
17-01-2022C51036283Part of Tier1 customegroup
24-01-2022C51036283Part of Tier1 customegroup
31-01-2022C51036283Part of Tier1 customegroup
03-01-2022C91078175Part of Tier2 customegroup
10-01-2022C91078175Part of Tier2 customegroup
17-01-2022C91078175Part of Tier2 customegroup
24-01-2022C91078175Part of Tier2 customegroup
31-01-2022C91078175Part of Tier2 customegroup
03-01-2022C41081262Part of Tier1 customegroup
10-01-2022C41081262Part of Tier1 customegroup
17-01-2022C41081262Part of Tier1 customegroup
24-01-2022C41081262Part of Tier1 customegroup
31-01-2022C41081262Part of Tier1 customegroup
03-01-2022C31114451Part of Tier1 customegroup
10-01-2022C31114451Part of Tier1 customegroup
17-01-2022C31114451Part of Tier1 customegroup
24-01-2022C31114451Part of Tier1 customegroup
31-01-2022C31114451Part of Tier1 customegroup
03-01-2022C71154144Part of Tier2 customegroup
10-01-2022C71154144Part of Tier2 customegroup
17-01-2022C71154144Part of Tier2 customegroup
24-01-2022C71154144Part of Tier2 customegroup
31-01-2022C71154144Part of Tier2 customegroup
03-01-2022C101160933Part of Tier2 customegroup
10-01-2022C101160933Part of Tier2 customegroup
17-01-2022C101160933Part of Tier2 customegroup
24-01-2022C101160933Part of Tier2 customegroup
31-01-2022C101160933Part of Tier2 customegroup
03-01-2022C61197522Part of Tier2 customegroup
10-01-2022C61197522Part of Tier2 customegroup
17-01-2022C61197522Part of Tier2 customegroup
24-01-2022C61197522Part of Tier2 customegroup
31-01-2022C61197522Part of Tier2 customegroup
03-01-2022C81275611Part of Tier2 customegroup
10-01-2022C81275611Part of Tier2 customegroup
17-01-2022C81275611Part of Tier2 customegroup
24-01-2022C81275611Part of Tier2 customegroup
31-01-2022C81275611Part of Tier2 customegroup