Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, need some help with the following:
The Visual
The visual shows the ranking of items based on the last time they were sold ('worst' to 'best')
The ‘PartID’ field is a column from the material table – ‘Products’. The other three fields are measures.
The ‘Cumulative Gap’ measure is a running total of the ‘Gap From Last Transaction’ measure.
Without using a slicer, the visual shows correct results for all the fields.
The problem
When adding a slicer, and adding ALLSELECTED to the measures, the ‘Gap From Last Transaction’ results are correct but the ‘Cumulative Gap’ shows the whole total in each cell instead of running total (left image)
If I remove ALLSELECTED from the measures, the ‘Cumulative Gap’ works as expected, but the results of the ‘Gap From Last Transaction’ are incorrect (obviously, because I removed ALLSELECTED) (right image).
these are the measures (i used index column similar to date column):
Gap From Last Transaction =
VAR MaxIndex = MAX ( Sales [ Index ] )
VAR LastBuy = CALCULATE( MAX ( Sales [ Index] ) ,
FILTER (ALLSELECTED (Sales) ,
Sales [ PartID ] = FIRSTNONBLANK( Products [ PartID ] , Products [ PartID ] ) ) )
RETURN
LastBuy - MaxIndex + 1
Total Gaps = SUMX ( Products , [ Gap From Last Transaction ] )
Cumulative Gap =
CALCULATE ( [ Total Gaps ] ,
FILTER (
ALLSELECTED( Products [ PartID ] ), SUMX ( Products , [ Total Gaps ] ) <= [ Total Gaps ] ))
Rank =
CALCULATE ( RANKX ( ALLSELECTED ( Products [ PartID] ) ,[Gap From Last Transaction ] ,,DESC ) )
Tanks for the help.
Solved! Go to Solution.
Hi, please review this PBIX.
Regards
Victor
Lima - Peru
Hi Victor.
Many thanks for your fast response and sample file.
Your solution works perfectly!
Definitely, one of the best features of Power Bi Desktop is this forum.
Best Regards,
Dan
Thank you, your very clear description of the scenario helped me a lot.
Regards
Victor
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |