How to show top 3 countries based on revenue?

[TusharGaurav]

Hi Experts,

I have the following table :

I need to show top 3 countries with their revenue corresponding to the Product No. e.g.

Can you please suggest ,how I can acheive this.

Thanks and Regards,

Tushar Gaurav

[Jai-Rathinavel · Accepted solution]

Hi @TusharGaurav To achieve your expected output please create Two measures as below, I have used ALLSELECTED to make the Ranking Dynamic based on filter selection. 

Total Revenue = MAX('Table'[Revenue]) --Replace MAX with SUM for total revenue

Top 3 Country Revenue = 
var _rnk = RANKX(
    ALLSELECTED('Table'[Country]),
    [Total Revenue],
    ,
    DESC,
    DENSE
)
RETURN
IF(
    HASONEVALUE('Table'[Country]),
    IF(_rnk <= 3, [Total Revenue]))

Input:

Output:

Hope it Helps 🙂 Appreciate a Kudos !

Thanks,

Jai Rathinavel | LinkedIn

[mdaatifraza5556 · Accepted solution]

Hi @TusharGaurav 

Can you please try the below DAX.(Create calculated table for this)

Top3ByMaxRevPerCountry =

TOPN(

    3,

    ADDCOLUMNS(

        SUMMARIZE('Table', 'Table'[Country]),

        "MaxRev", CALCULATE(MAX('Table'[revenue]))

    ),

    [MaxRev],

    DESC

)

If this answers your questions, kindly accept it as a solution and give kudos.

[TusharGaurav]

Thanks a lot for your help and prompt response

[TusharGaurav]

Hi Akash,

Thanks for your response.

However here I am not the total revenue corresponding to the country.

Here I need to show the revenue corresponding to the top product for a country, for example the total revenue for Country "C1" is 3215 but I need to show revenue corresponding to top product for this country i.e. 1110.

Thanks and Regards,

Tushar Gaurav

[Jai-Rathinavel · Accepted solution]

Hi @TusharGaurav To achieve your expected output please create Two measures as below, I have used ALLSELECTED to make the Ranking Dynamic based on filter selection. 

Total Revenue = MAX('Table'[Revenue]) --Replace MAX with SUM for total revenue

Top 3 Country Revenue = 
var _rnk = RANKX(
    ALLSELECTED('Table'[Country]),
    [Total Revenue],
    ,
    DESC,
    DENSE
)
RETURN
IF(
    HASONEVALUE('Table'[Country]),
    IF(_rnk <= 3, [Total Revenue]))

Input:

Output:

Hope it Helps 🙂 Appreciate a Kudos !

Thanks,

Jai Rathinavel | LinkedIn

[TusharGaurav]

Thanks for the prompt response.

This is what I was looking for.

[Akash_Varuna]

Hi @TusharGaurav  Create a measure to calculate total revenue

Total Revenue = SUM(TableName[Revenue])

Then, create a ranking measure to rank countries.

Rank by Revenue = RANKX(ALL(TableName[Country]), [Total Revenue], , DESC)

 Add a table visual displaying Country and Total Revenue and filter it to show Rank by Revenue <= 3.