Forum Discussion

Mking4646's avatar
Mking4646
Frequent Visitor
3 years ago

GrandTotal Issue

I'm assign Staff roles to customers based on customers spend, I have created a measure that assigned the roles and created individual role measures in order to show the revenue generated.

 

My issue is with the grand total it naturally assigns a role and therefore only calculates the grandtotal for that row

 

I want the grandtotal to show for AccMan and MSERV roles as well as Digi role

 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Helpo Mking4646 

    Please try

     

    Correct Total = IF(HASONEFILTER(table[Custom 1]),[Your Measure],SUMX(VALUES(table[Custom 1),[Your Measure]))
    • Mking4646's avatar
      Mking4646
      Frequent Visitor
      3 years ago

      Unfortunately it's numbers are off with the original numbers

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hmm try to change the column name in the formula i gave you like use 

        Correct Total = IF(HASONEFILTER(table[sub region]),[Your Measure]

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Mking4646 Just chiming in here that VALUES may get you in trouble and you might want to consider DISTINCT instead. Also, here are some additional resources so you can understand what is going on. This looks like a measure totals problem. Very common. See my 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

    • Mking4646's avatar
      Mking4646
      Frequent Visitor
      3 years ago

      Thanks, I'll take a look at those resources ASAP.

       

      the reason the totals seem to be wrong is because the grand total for "role assign" is returning "digi" the individual role revenues is seeing that as "digi" and therefore on "accman" and "mserv" aren't showin a total as neither is "digi"

       I'm comfotable with why it's not outputting a grand total just struggling to see a way around it