Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Created one measure for sales "[Test]" and showing the "Top" & "Bottom" items based on the Slicer ,
1) Total is showing only for "Top 10" not for "Bottom 10"
2) Total is wrong , it should be "118K" not "170K"
searched and tried many option and nothing worked, Tried the option from one of the discussion using "hasonfilter"
Solved! Go to Solution.
Got a solution, instead of calculating the Measure for total, created a measure for "Filter" based on the "Top 10" and "Bottom 10" ranking then put it on the Visual filter , now the total is matching and showing the total for "Top" & "Bottom" selection
Agency_Filter =
Var STop = if(ISBLANK(Sale_Table[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,DESC,Dense))
Var SBott = if(ISBLANK(Sale_Table[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,ASC,Dense))
Return
if(and(SELECTEDVALUE('Rank'[Rank])="Agency",SELECTEDVALUE('Top'[Top])="Top 10"),STop,IF(and(SELECTEDVALUE('Rank'[Rank])="Agency",SELECTEDVALUE('Top'[Top])="Bottom 10"),SBott,1))
Got a solution, instead of calculating the Measure for total, created a measure for "Filter" based on the "Top 10" and "Bottom 10" ranking then put it on the Visual filter , now the total is matching and showing the total for "Top" & "Bottom" selection
Agency_Filter =
Var STop = if(ISBLANK(Sale_Table[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,DESC,Dense))
Var SBott = if(ISBLANK(Sale_Table[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,ASC,Dense))
Return
if(and(SELECTEDVALUE('Rank'[Rank])="Agency",SELECTEDVALUE('Top'[Top])="Top 10"),STop,IF(and(SELECTEDVALUE('Rank'[Rank])="Agency",SELECTEDVALUE('Top'[Top])="Bottom 10"),SBott,1))
This looks like a measure totals problem. Very common. See @Greg_Deckler post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also, you might find MM3TR&R helpful: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thank you so much @VahidDM ,
Probelm 2) is solved but could n't figur it out why total is not showing when I select "Bottom 10",
No difference in measure - only "ASC" & "DESC" ,
Rank Bottom =
Var Agency = if(ISBLANK(f_PC_SALES[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,ASC,Dense))
return
IF(SELECTEDVALUE(IF(SELECTEDVALUE('Rank'[Rank])="Agency",Agency,[Sales]))
Rank Top =
Var Agency = if(ISBLANK(f_PC_SALES[Sales]),BLANK(),RANKX(ALLSELECTED(Product[Agency]),[Sales],,DESC,Dense))
return
IF(SELECTEDVALUE(IF(SELECTEDVALUE('Rank'[Rank])="Agency",Agency,[Sales]))
User | Count |
---|---|
56 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |