Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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 ) ) ) )
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.
Use following dax
Direct Sum of TopN =
CALCULATE (
SUM ( Table3[Value] ),
TOPN (
5,
GROUPBY ( Table3, Table3[Category] ),
CALCULATE ( SUM ( Table3[Value] ) )
)
)
Hello @sjoerdbuis
I updated your .pbix file with the needed additions.
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.
What do you mean @sjoerdbuis ? My top 5 list matches the top 5 list you asked for in your example:
Hi
Yes your shown output is great,
but when i go the metrix and revalidate it with enter it gives a syntax error?
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.
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)
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]))
are you able to re-edit the measure according to my example file?
I am not that of a DAX wizard yet.
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!
I was modifying one of my models using the code from @tex628 and ran into a couple of tweaks being needed.
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.
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 ) ) ) )
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |