Forum Discussion
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?
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 ) ) ) )
18 Replies
- tex628
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])) - jdbuchanan71
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.
- jdbuchanan71
Hello Anonymous
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.
Credit for all this logic goes to Lukas Lotters who gave us this excellent post.
- jdbuchanan71
What do you mean Anonymous ? My top 5 list matches the top 5 list you asked for in your example:
