Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I created a top N for a bar chart, but I wanted to see a column with the others? it's possible?
And the method I'm using works, but if I put a field on legend it no longer does the TopN, any suggestions?
I created a parameter (this is my topN slicer) and after i created this measure:
TOP N = VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) RETURN SWITCH(TRUE(), SelectedTop = 0, 'Teste'[Sum Qty], RANKX ( ALLSELECTED( 'Teste'[Product] ), 'Teste'[Sum Qty] ) <= SelectedTop, 'Teste'[Sum Qty] )
This is the test file: https://1drv.ms/u/s!AkZ8wrQeLhnChQLnr0qzvPlk39sR
Kind regards.
Solved! Go to Solution.
Hi @Anonymous,
Using this post by @TomMartens, you can get the solution.
I have made some adjustments to get the TOPN based on your selection but the rest is from the post.
Products_and_Others = UNION ( SUMMARIZE ( ALL ( Teste[Product] ); Teste[Product] ); ROW ( "Product"; "Other" ) )
top 5 and other = VAR SelectedTop = SELECTEDVALUE ( 'TopN'[TopN] ) VAR top5 = CALCULATETABLE ( TOPN ( SelectedTop; VALUES ( Teste[Product] ); CALCULATE ( SUM ( Teste[Qty] ) ) ) ) VAR other = ROW ( "Product"; "Other" ) VAR allTheRest = CALCULATE ( SUM ( Teste[Qty] ); EXCEPT ( VALUES ( Teste[Product] ); top5 ) ) VAR theUnion = UNION ( top5; other ) RETURN SUMX ( INTERSECT ( 'Products_and_Others'; theUnion ); VAR currentIterator = 'Products_and_Others'[Product] RETURN IF ( 'Products_and_Others'[Product] <> "Other"; CALCULATE ( SUM ( Teste[Qty] ); Teste[Product] = currentIterator ); allTheRest ) )
Then use the unrelated table on the x-axis and this measure on the data values.
PBIX file attach, I have highlited the Others on the Data Colors.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI can't get this to work at all. Only when I slide the slicer to 1 does it show the top result and other, but from 2-10 it just shows "Other"
Hi @wma2323
Can you share the metric you are using please.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsQuick question before I do that. If there is a blank in the product list with other, will that throw off the calc?
Yes it can do it.
Blank values always can add issues to your calculation
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hi @wma2323 ,
You issue is with the SELECTEDVALUE(TopN) part and the way the slicer is used.
SELECTEDVALUE only returns a single value from the data, so in case you have more than one value on your selection (has you have in the second image) the result is blank, so you get only a row for others.
In this case you need to change your slicer to be a single select one or change the formula to a MAX instead of SelectedVAlue
VAR SelectedTop =
MAX('TopN'[TopN])
If you replace the variable you will get expected result.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThat did it! Thanks!
Hi @Anonymous,
Using this post by @TomMartens, you can get the solution.
I have made some adjustments to get the TOPN based on your selection but the rest is from the post.
Products_and_Others = UNION ( SUMMARIZE ( ALL ( Teste[Product] ); Teste[Product] ); ROW ( "Product"; "Other" ) )
top 5 and other = VAR SelectedTop = SELECTEDVALUE ( 'TopN'[TopN] ) VAR top5 = CALCULATETABLE ( TOPN ( SelectedTop; VALUES ( Teste[Product] ); CALCULATE ( SUM ( Teste[Qty] ) ) ) ) VAR other = ROW ( "Product"; "Other" ) VAR allTheRest = CALCULATE ( SUM ( Teste[Qty] ); EXCEPT ( VALUES ( Teste[Product] ); top5 ) ) VAR theUnion = UNION ( top5; other ) RETURN SUMX ( INTERSECT ( 'Products_and_Others'; theUnion ); VAR currentIterator = 'Products_and_Others'[Product] RETURN IF ( 'Products_and_Others'[Product] <> "Other"; CALCULATE ( SUM ( Teste[Qty] ); Teste[Product] = currentIterator ); allTheRest ) )
Then use the unrelated table on the x-axis and this measure on the data values.
PBIX file attach, I have highlited the Others on the Data Colors.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |