Forum Discussion

Dtrain's avatar
Dtrain
Icon for Helper I rankHelper I
5 years ago
Solved

How to correct measure total

I’m stuck with this, the total is not showing the correct total. I am use the below measure and is working but for the total. Could anyone please help?   Total Bonus = VAR AllBonus = SUM('table 1'...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Dtrain , You need to use group by of visual and create thsi formula assume, you are using ApprovedCustId in visual or use somthing else which is on row or non summarized

     

    Total Bonus =
    VAR AllBonus = SUM('table 1'[Sale Bonus Amount])
    VAR ApprovedCust = SELECTEDVALUE('table 1' [ApprovedCustId])
    VAR BonusEx = CALCULATE(SUM('table 1' [Sale Bonus Amount]) *2, 'table 1' [Sale Bonus Amount] = AllBonus, 'table 1' [ApprovedCustId] = ApprovedCust)
    RETURN
    sumx(values( Table[ApprovedCustId]), IF(ISBLANK(ApprovedCust), AllBonus, BonusEx))

     

    for more than one column use summarize

     

    Total Bonus =
    VAR AllBonus = SUM('table 1'[Sale Bonus Amount])
    VAR ApprovedCust = SELECTEDVALUE('table 1' [ApprovedCustId])
    VAR BonusEx = CALCULATE(SUM('table 1' [Sale Bonus Amount]) *2, 'table 1' [Sale Bonus Amount] = AllBonus, 'table 1' [ApprovedCustId] = ApprovedCust)
    RETURN
    sumx(Summarize( Table[ApprovedCustId],"_1", IF(ISBLANK(ApprovedCust), AllBonus, BonusEx)),[_1])

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Dtrain , You need to use group by of visual and create thsi formula assume, you are using ApprovedCustId in visual or use somthing else which is on row or non summarized

 

Total Bonus =
VAR AllBonus = SUM('table 1'[Sale Bonus Amount])
VAR ApprovedCust = SELECTEDVALUE('table 1' [ApprovedCustId])
VAR BonusEx = CALCULATE(SUM('table 1' [Sale Bonus Amount]) *2, 'table 1' [Sale Bonus Amount] = AllBonus, 'table 1' [ApprovedCustId] = ApprovedCust)
RETURN
sumx(values( Table[ApprovedCustId]), IF(ISBLANK(ApprovedCust), AllBonus, BonusEx))

 

for more than one column use summarize

 

Total Bonus =
VAR AllBonus = SUM('table 1'[Sale Bonus Amount])
VAR ApprovedCust = SELECTEDVALUE('table 1' [ApprovedCustId])
VAR BonusEx = CALCULATE(SUM('table 1' [Sale Bonus Amount]) *2, 'table 1' [Sale Bonus Amount] = AllBonus, 'table 1' [ApprovedCustId] = ApprovedCust)
RETURN
sumx(Summarize( Table[ApprovedCustId],"_1", IF(ISBLANK(ApprovedCust), AllBonus, BonusEx)),[_1])

Anonymous's avatar
Anonymous
Not applicable
4 years ago

have a doubt related to this if we are crating a new measure to insure that total come correct should we use the correct total measure in bar and line chart visual also or we can use the same measure which is being used in table visual  and giving the total value wrong but indivisual row value correct