Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swwong1
Helper III
Helper III

% of Top 10 Revenue using TOPN

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!

 
 
6 REPLIES 6
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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
Community Champion
Community Champion

@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)))

 

Снимок.PNG

 

See pbix-file in attach

 

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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!

 

swwong1_0-1595166167477.png

 

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.

top10.png

 

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


thank, let me have a look at that.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.