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
sjoerdbuis
Helper I
Helper I

Pie Chart - Top 5 - Rest named Other

https://www.dropbox.com/s/z2job1mric80n0d/Top%205%20Other.pbix?dl=0

 

Hi all,

Please find attached my Dashboard.

Column, Date, Amount, Company

I am looking for a Pie Chart showing the Top 5 where the Top <5 is displayed as Other.

As the Pie Chart is used over time, ofcourse the Top 5 changes over time and so does the Other group ofcourse...

 

Any suggestions?

 

 

 

 

 

1 ACCEPTED SOLUTION

You have TOPN(5. There is a period where there should be a comma.

You will also need to join the [Companies] table to the [Transacties] table.

You should also change the formula to only return $ for the top 5 or other, we just need to add an intersect to @tex628 excellent work.

TopN = 
VAR TOP_N =
    CALCULATETABLE ( 'Companies', TOPN ( 5, ALL ( 'Companies' ), [Amount2] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Companies'[Company] ) = "Other",
        CALCULATE ( [Amount2], ALL ( 'Companies' ) ) - CALCULATE ( [Amount2], TOP_N ),
        IF (
            SELECTEDVALUE ( Companies[Company] ) = BLANK (),
            CALCULATE ( [Amount2], ALL ( 'Companies' ) ),
            CALCULATE ( [Amount2], INTERSECT ( Companies, TOP_N ) )
        )
    )

top5fast.jpg

View solution in original post

18 REPLIES 18
jdbuchanan71
Super User
Super User

There is an explicit row for "Other" in the calculated Companies table.

Companies = 
UNION (
    DISTINCT(
        SELECTCOLUMNS(
            Transacties,
            "Company",Transacties[Company]
        )
    ),
    ROW("Company","Other")
)

The last line of the DAX for the calculated table adds the Other row.

Anonymous
Not applicable

Use following dax

Direct Sum of TopN =
CALCULATE (
SUM ( Table3[Value] ),
TOPN (
5,
GROUPBY ( Table3, Table3[Category] ),
CALCULATE ( SUM ( Table3[Value] ) )
)
)

 

jdbuchanan71
Super User
Super User

Hello @sjoerdbuis 

I updated your .pbix file with the needed additions.

  • A calculated table holding the unique companies and an 'Other' row.
  • The measures for Amount, Top5, Other, Top 5 + Other

https://www.dropbox.com/s/tocvgtmsqpwxneg/Top%205%20Other.pbix?dl=0

Because the amounts were negative it was acting a bit odd and pulling only 4 when I asked for "top" 5 ( I think bacause of the blank empty row DAX adds to tables ) so I had to trick it to pull "Top" 6 which gave us 5 amounts.

top5.jpg

Credit for all this logic goes to Lukas Lotters who gave us this excellent post.

https://www.oraylis.de/blog/show-top-n-and-rest-in-power-bi

hi it seems like the syntax for your top5 metric is incorrect.

Can you take a look?

@jdbuchanan71 

What do you mean @sjoerdbuis ?  My top 5 list matches the top 5 list you asked for in your example:

top5.jpg

Hi

@jdbuchanan71 

Yes your shown output is great,

but when i go the metrix and revalidate it with enter it gives a syntax error?Top 5.JPG

 

 

When I download the file it is working, try downloading it again.  I even modified the measure by adding the comment on the end, works fine.

top5measure.jpg

@jdbuchanan71 

Could it be a version item?

I am working on: 2.68.5432.841 64-bit (april 2019)

 

strange that when i revalidate Top5 it gives me:

 

The syntax for 'ADDCOLUMNS' is incorrect. (DAX(IF ( HASONEVALUE( Companies[Company] ), CALCULATE( [Total Amount], FILTER( Transacties, [Company] = VALUES(Companies[Company]) && CONTAINS( TOPN ( 6. ADDCOLUMNS ( ALLNOBLANKROW( Companies[Company] ), "Top 5 Amount", CALCULATE( [Total Amount], FILTER ( Transacties, Transacties[Company] = EARLIER ( [Company] ) ) ) ), [Top 5 Amount], DESC ),Companies[Company], VALUES ( Companies[Company] ) ) ) )))).

 

Could be I guess.  I am working on 2.70.5494.761 64-bit (June 2019)

tex628
Community Champion
Community Champion

Hi,

This is taken from a similar calculation i did on top customers. It required me to add a record to the customer table that only holds "Other" as a CustomerName. I hope it can work for you or that you get some inspiration atleast! 

TopN = 
VAR TOP_N = CALCULATETABLE('Customer',TOPN(5, ALL('Customer'),[Amount]))
Return
IF(SELECTEDVALUE('Customer'[CustomerName])="Other",
CALCULATE([Amount],ALL('Customer'))
-
CALCULATE([Amount],TOP_N)
,
IF(SELECTEDVALUE('Customer'[CustomerName])=BLANK(),
CALCULATE([Amount],ALL('Customer')),
[Amount]))

Connect on LinkedIn

@tex628 

are you able to re-edit the measure according to my example file?

I am not that of a DAX wizard yet.

 

Can you add the companies as a dimension table?


Connect on LinkedIn

image.png

TopN = 
VAR TOP_N = CALCULATETABLE('Companies',TOPN(5, ALL('Companies'),[Amount]))
Return
IF(SELECTEDVALUE('Companies'[Company])="Other",
CALCULATE([Amount],ALL('Companies'))
-
CALCULATE([Amount],TOP_N)
,
IF(SELECTEDVALUE(Companies[Company])=BLANK(),
CALCULATE([Amount],ALL('Companies')),
[Amount]))
Amount = SUM(Transacties[Amounty]) * -1

The topN filter on the circle diagram should hold top 6 companies to include both the top 5 and the "Others". It needs to be TopN by the [TopN] value. 

I hope you get it to work. Otherwise just give me a shout and we'll take another look!


Connect on LinkedIn

@tex628 @sjoerdbuis 

I was modifying one of my models using the code from @tex628 and ran into a couple of tweaks being needed.

  1. If the table that holds the things we are bucketing (company, customer, category) has a blank, the total trap was putting the total amount on those lines as well as the total of the table.  Changed it to use NOT ISFILTERED
  2. If we are counting things that can appear in multiple categories, ie COUNTDISTINCT ( Customers[CustNum] ) the remaining calc was getting reduced by too much so I changed it to use EXCEPT
TopN
VAR Top_N =
    CALCULATETABLE ( Companies, TOPN ( 5, ALL ( 'Companies' ), [Amount2] ) )
RETURN
    IF ( 
        NOT ISFILTERED ( 'Companies'[Company] ), CALCULATE ( [Amount2], ALL ( 'Companies' ) ),
        IF ( SELECTEDVALUE ( 'Companies'[Company] ) = "Other",
            CALCULATE ( [Amount2], EXCEPT ( ALL ( 'Companies' ), Top_N ) ),
            CALCULATE ( [Amount2], INTERSECT ( 'Companies', Top_N ) )
        )
    )

Thought I would share these incase they might be helpful.

@tex628 

https://www.dropbox.com/s/hreafmni9y1sd9v/Top%205%20Otherv3.pbix?dl=0

 

I have a syntax error on the all function, find attached.

Maybe I am doing something wrong here?

 

You have TOPN(5. There is a period where there should be a comma.

You will also need to join the [Companies] table to the [Transacties] table.

You should also change the formula to only return $ for the top 5 or other, we just need to add an intersect to @tex628 excellent work.

TopN = 
VAR TOP_N =
    CALCULATETABLE ( 'Companies', TOPN ( 5, ALL ( 'Companies' ), [Amount2] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Companies'[Company] ) = "Other",
        CALCULATE ( [Amount2], ALL ( 'Companies' ) ) - CALCULATE ( [Amount2], TOP_N ),
        IF (
            SELECTEDVALUE ( Companies[Company] ) = BLANK (),
            CALCULATE ( [Amount2], ALL ( 'Companies' ) ),
            CALCULATE ( [Amount2], INTERSECT ( Companies, TOP_N ) )
        )
    )

top5fast.jpg

Hi Thanks for this solution it helped me, can you please explain how did this row worked 

SELECTEDVALUE ( 'Companies'[Company] ) = "Other" 

since you dont have explicit row for others?
It worked on my side too and I dont have other row either 

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.