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
BICrazy
Helper II
Helper II

Total to Add Up Measure 'Column' Instead of 'Row Formula'

Hello Power BI Community,

 

I'm at my wits' end and ready to pull my hair out.  Please can someone help with a Measure Total?  I'm using the following measure  which iterates at a row-level to calculate the desired result which is working as intended:

Revenue(Net of Activation & Claw Back) = 

//Avg Packages
VAR _Ave = CALCULATE(SUM('Daily'[Packages_Floor]),FILTER(Daily,Daily[Group] <> "MANAGER"),USERELATIONSHIP('Calendar'[Date],Daily[Month]))
VAR _Avg = CALCULATE(DIVIDE(_Ave,[Elapsed Days],0))

//RPS
VAR _RPS = CALCULATE(AVERAGE('Monthly Targets'[Revenue Per Sale]),FILTER(Daily,Daily[Group] <> "MANAGER"))

//Activation Rate
VAR _XA = SUMMARIZE ( 'Monthly Targets','Monthly Targets'[Campaign], "AR", MAX ('Monthly Targets'[Activation Rate] ))
VAR _AR = AVERAGEX ( _XA,[AR])

//Clawback
VAR _XC = SUMMARIZE ( 'Monthly Targets','Monthly Targets'[Campaign], "Claw", MAX ('Monthly Targets'[Clawback] ))
VAR _CLAW = AVERAGEX ( _XC,[Claw])

//CALCULATION
VAR _EXCLCLAWBACK = _Avg * _RPS * _AR
VAR _INCLCLAWBACK = _Avg * _RPS * _AR * _CLAW

VAR _LESSCLAW = (_EXCLCLAWBACK - _INCLCLAWBACK)

RETURN
_LESSCLAW

Capture1.JPGCapture.JPG 

I've also attempted Returning the Isfiltered and HasOneValue functions (below) but this did not resolve the problem.

// IF(ISFILTERED(Daily[Campaign]),_LESSCLAW, AVERAGEX(VALUES(Daily[Campaign]),_LESSCLAW))

// IF(HASONEVALUE('Monthly Targets'[Campaign]),
//     _LESSCLAW,
//         AVERAGEX( VALUES( 'Monthly Targets'[Campaign] ), _LESSCLAW))



Is there a way to Sum up the column totals in this measure, instead of it performing the calculation at a row level?
Basically, the R506,896 should read R259,157, and the R2,076,791 should add read R1,817,704.

Sorry to say, that I know what the problem is, but I don't know how to fix it.

Appreciate the help from this community 🙂 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

New measure =sumx(values(table[campaign]),[old measure])

 

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

New measure =sumx(values(table[campaign]),[old measure])

 

@wdx223_Daniel  - Worked like a bomb.  Thank you

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.

Top Solution Authors