Forum Discussion

Irshadn03's avatar
Irshadn03
Frequent Visitor
4 years ago
Solved

Table Total is wrong

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

 

 

  • Irshadn03's avatar
    Irshadn03
    4 years ago

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

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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/

     

     

    • Irshadn03's avatar
      Irshadn03
      Frequent Visitor
      4 years ago

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

       





  • Irshadn03's avatar
    Irshadn03
    Frequent Visitor
    4 years ago

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