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 community
I have followed this great material by SQLBI to do a dynamic TopN & Others solution : https://www.sqlbi.com/tv/implementing-the-top-n-and-others-pattern-using-calculation-groups-in-dax-u...
I have a issue with incorrect Ranking. I can't figure out why.. Any ideas what to do? In some filter cases it return correct ranking, but mostly incorrect.
Relevant measures below:
Ranking =
IF (
ISINSCOPE ( 'Customer Names'[Customer_Name] ),
VAR CustomersToRank = [TopN Value]
VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] )
VAR Val = [ValueForRanking]
VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others"
RETURN
IF (
IsOtherSelected,
-- Rank for Others
CustomersToRank + 1,
-- Rank for regular Customers
IF (
Val > 0,
VAR VisibleCustomers =
CALCULATETABLE (
VALUES ( 'pbi DimCustomer' ),
ALLSELECTED ( 'Customer Names' )
)
VAR Ranking =
RANKX ( VisibleCustomers, [ValueForRanking], Val )
RETURN
IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking )
)
)
)
ValueForRanking =
VAR MeasureForRanking =
SELECTEDVALUE ( 'Ranking measure'[Measure to use] )
VAR Val =
SWITCH (
TRUE,
MeasureForRanking = "Sales", [Sales],
MeasureForRanking = "Order intake", [Order intake],
MeasureForRanking = "Order bank", [Order bank],
[Sales]
)
RETURN
Val
Calculation item:
IF (
NOT ISSELECTEDMEASURE ( [Ranking] ),
VAR ValueOfAll =
CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Customer Names' ) )
RETURN
IF (
NOT ISINSCOPE ( 'Customer Names'[Customer_Name] ),
-- Calculation for a group of products
ValueOfAll,
-- Calculation for one product name
VAR CustomersToRank = [TopN Value]
VAR ValueOfCurrentCustomer =
SELECTEDMEASURE ()
VAR IsOtherSelected =
SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others"
RETURN
IF (
NOT IsOtherSelected,
-- Calculation for a regular product
VAR Ranking = [Ranking]
VAR Result =
IF (
NOT ISBLANK ( Ranking ),
( Ranking <= CustomersToRank ) - ( Ranking = CustomersToRank + 1 )
)
RETURN
IF ( Result, ValueOfCurrentCustomer ),
-- Calculation for Others
VAR VisibleCustomers =
CALCULATETABLE (
VALUES ( 'pbi DimCustomer' ),
ALLSELECTED ( 'Customer Names'[Customer_Name] )
)
VAR CustomersWithValues =
ADDCOLUMNS (
VisibleCustomers,
"@ValForRanking", [ValueForRanking],
"@SelMeasureAmount", SELECTEDMEASURE ()
)
VAR ValueOfTopCustomers =
SUMX (
TOPN ( CustomersToRank, CustomersWithValues, [@ValForRanking] ),
[@SelMeasureAmount]
)
VAR ValueOfOthers = ValueOfAll - ValueOfTopCustomers
RETURN
ValueOfOthers
)
),
SELECTEDMEASURE ()
)
Would appreicate any ideas/help to fix this
Thanks
Bump. Please let me know if more information is needed for understanding context.
Hi @Nikolainoergard ,
Based on the description, please try to confirm the filter applied isn’t affecting the ranking measure.
Then, review the Ranking measure formula. Try to use the all dax in the VisibleCustomers variable.
IF (
Val > 0,
VAR VisibleCustomers =
CALCULATETABLE (
VALUES ( 'pbi DimCustomer' ),
ALL( 'Customer Names' )
)
VAR Ranking =
RANKX ( VisibleCustomers, [ValueForRanking], Val )
RETURN
IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking )
)
RANKX function (DAX) - DAX | Microsoft Learn
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your message
I have tried diffrent options regarding applied filters.
1) Removing Calc. item currency conversion impact (Using hardcoded measure)
2) Removing Date filter
3) Using ALL instead of AALSELECTED in Ranking measure
..And unfortunately i still get wrong ranking
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 |
---|---|
46 | |
28 | |
23 | |
12 | |
8 |
User | Count |
---|---|
76 | |
51 | |
45 | |
16 | |
12 |