topic Re: Table Total is wrong in DAX Commands and Tips

Table Total is wrong

Irshadn03 — Sun, 05 Dec 2021 11:44:19 GMT

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"

IF(HASONEFILTER(Rank[Rank]),[test], SUMX(Product, [Test]))

Where "[Rank]" is the slicer table "Agency,MG1,MG2..." in the screenshot and "Product" is the Dimension table used for below table. "[Test]" is the "[Selected MTD Sales]"

Re: Table Total is wrong

VahidDM — Sun, 05 Dec 2021 12:18:45 GMT

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/

Re: Table Total is wrong

Irshadn03 — Sun, 05 Dec 2021 13:12:35 GMT

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]))

Re: Table Total is wrong

Irshadn03 — Mon, 06 Dec 2021 06:58:55 GMT

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))