% of Top 10 Revenue using TOPN

[swwong1]

Hi All

I have used the TopN visual filter to show my top 10 customers. What I would like to do is to include the % of the top 10 revenue along with the full name of client as a data label for a treemap.    Example: Mr. Maurice Shan (11%)

Using the TopN, I have created a measure but it seems like the total is different from the total of the visual. Can anyone please advise?

https://www.dropbox.com/s/psfi50d6apbc7mm/TOP%20N.pbix?dl=0 

Thanks!

[az38]

Hi @swwong1 

I didn't dive deeply in your data but what I have noticed.

GAP in top calulculation appers on the sixth position - Mr. Franklin Xu.

TOP-5 works good, TOP-6 already has a difference.

So, if go further I've noticed that Mr.Franklin Xu has 2 rows in Customer dimension table. There are 2different persons with the same name

Maybe somewhere you have lookup by FullName

[swwong1]

@az38 

I have used customer key for my top N visual filter instead and it now matches the DAX I wrote.

Could you please advise how I can 'freeze' the total top 10 revenue

I am trying to work out the % of the top 10 revenue for each top 10 client.

Thanks!

[az38]

@swwong1 

I created new Top Table, related it to AS_Sales

Now, I'm sure your Top 10 Client Revenue is correct

So, % of top10 can look like

% of Top 10 Client Revenue = DIVIDE([Top 10 Client Revenue], CALCULATE([Total Revenue], ALL(AW_Sales)))

See pbix-file in attach

[swwong1]

Apologies for the very late reply.

What I wanted to calculate is the % of top 10 revenue for each client. For example, Mr. Maurice Shan should be around 11%

Thanks!

[mahoneypat]

Please try these two measures.  They do not require the use of the Top 10 table (so you can delete that).  You can add them in a table visual with your original FullName and CustomerKey columns from your Customer Look up table.

Top 10 Total =
VAR top10table =
    TOPN (
        10,
        ALL ( AW_Customers_Lookup[CustomerKey], AW_Customers_Lookup[FullName] ),
        [Total Revenue], DESC
    )
RETURN
    CALCULATE ( [Total Revenue], KEEPFILTERS ( top10table ) )


Pct of Top 10 Revenue =
VAR top10table =
    TOPN (
        10,
        ALL ( AW_Customers_Lookup[CustomerKey], AW_Customers_Lookup[FullName] ),
        [Total Revenue], DESC
    )
RETURN
    DIVIDE (
        CALCULATE ( [Total Revenue], KEEPFILTERS ( top10table ) ),
        CALCULATE ( [Total Revenue], top10table )
    )

You will get this result.  I left the original Total Revenue measure in for comparison when I wrote the new measures.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[swwong1]

thank, let me have a look at that.