Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dtrain
Helper I
Helper I

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'[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

IF(ISBLANK(ApprovedCust), AllBonus, BonusEx)

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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
Not applicable

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 

Thank you for your support

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.