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.
Hello,
unfortunately PBI doesn't let you chose TOPN with "Others", when I use that function in the inbuild filter panel its will only show the TOPN number ratio without the "others".
In my example I have a table with three companies:
Supplier | Date | Bookingnumbers | ||
Company A | 01.01.2021 | 23424 | ||
Company A | 02.01.2021 | 16372 | ||
Company A | 03.01.2021 | 2645 | ||
Company B | 04.01.2021 | 23455 | ||
Company B | 05.01.2021 | 6565 | ||
Company B | 06.01.2021 | 35345 | ||
Company B | 07.01.2021 | 76575 | ||
Company C | 08.01.2021 | 345435 | ||
Company C | 09.01.2021 | 745745 | ||
Company C | 10.01.2021 | 3453465 |
I tried to calculate a pie chart with the companies as a dimension and the unique count of the bookingnumbers of the top 2 and the rest is displayed as the "others".
Help is very appreciated.
Best.
Solved! Go to Solution.
Hi @Applicable88 ,
Create a calculated column to take the company name according to the top two in the ranking, and change the other company names to "other". For example:
Column =
var _rank = rankx('Table',CALCULATE(SUM('Table'[Bookingnumber]),FILTER('Table','Table'[Supplier]=EARLIER('Table'[Supplier]))),,,Dense)
return
IF(_rank<=2,'Table'[Supplier],"other")
Then use this column in pie chart instead of [supplier].
Best Regards,
Jay
Here is one way to make it dynamic.
1) create a table with the supplier & a row for "Other".
TopN Table =
VAR Supplier = DISTINCT('Table'[Supplier])
VAR Other = {"Other"}
Return
UNION(Supplier, Other)
Create an inactive relationhsip between this table an the supplir filed in the main fact table:
2) now the following measures:
Booking number Distinct count = DISTINCTCOUNT('Table'[Bookingnumbers])
Rnk =
IF (
SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
1,
RANKX (
ALL ( 'TopN Table'[Supplier] ),
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
)
)
DistinctCount TopN =
VAR _All =
CALCULATE ( [Booking number Distinct count], ALL ( 'Table'[Supplier] ) )
VAR Supplier =
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
RETURN
IF (
SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
_All
- SUMX (
FILTER (
ALL ( 'TopN Table' ),
[Rnk] <= SELECTEDVALUE ( 'TopN Value'[TopN Value] )
),
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
),
Supplier
)
Final Measure = IF([rnk] <= SELECTEDVALUE('TopN Value'[TopN Value]), [DistinctCount TopN])
This is intended to work on a selection of the TopN value from a slicer (created using the "New Parameter" option under Modeling. If it's to remain static, you can hard code the value instead of the SELECTEDVALUE ( 'TopN Value'[TopN Value] ) in the measures.
I've attached a sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
Here is one way to make it dynamic.
1) create a table with the supplier & a row for "Other".
TopN Table =
VAR Supplier = DISTINCT('Table'[Supplier])
VAR Other = {"Other"}
Return
UNION(Supplier, Other)
Create an inactive relationhsip between this table an the supplir filed in the main fact table:
2) now the following measures:
Booking number Distinct count = DISTINCTCOUNT('Table'[Bookingnumbers])
Rnk =
IF (
SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
1,
RANKX (
ALL ( 'TopN Table'[Supplier] ),
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
)
)
DistinctCount TopN =
VAR _All =
CALCULATE ( [Booking number Distinct count], ALL ( 'Table'[Supplier] ) )
VAR Supplier =
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
RETURN
IF (
SELECTEDVALUE ( 'TopN Table'[Supplier] ) = "Other",
_All
- SUMX (
FILTER (
ALL ( 'TopN Table' ),
[Rnk] <= SELECTEDVALUE ( 'TopN Value'[TopN Value] )
),
CALCULATE (
[Booking number Distinct count],
USERELATIONSHIP ( 'TopN Table'[Supplier], 'Table'[Supplier] )
)
),
Supplier
)
Final Measure = IF([rnk] <= SELECTEDVALUE('TopN Value'[TopN Value]), [DistinctCount TopN])
This is intended to work on a selection of the TopN value from a slicer (created using the "New Parameter" option under Modeling. If it's to remain static, you can hard code the value instead of the SELECTEDVALUE ( 'TopN Value'[TopN Value] ) in the measures.
I've attached a sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown there is indeed many use cases for that dynamic version of yours. Thank you so much.
Best.
@PaulDBrown , sorry for the late reply.
Thank you very much for the effort and explanation. Fits perfectly!
Hi @Applicable88 ,
Create a calculated column to take the company name according to the top two in the ranking, and change the other company names to "other". For example:
Column =
var _rank = rankx('Table',CALCULATE(SUM('Table'[Bookingnumber]),FILTER('Table','Table'[Supplier]=EARLIER('Table'[Supplier]))),,,Dense)
return
IF(_rank<=2,'Table'[Supplier],"other")
Then use this column in pie chart instead of [supplier].
Best Regards,
Jay
Hello @Applicable88
You can create a calculated column and use the same in the Pie Chart.
@Anonymous How would be implemented. Via TOPN or kind of ranking function?
Can you show me example?
Covering 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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |