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

BICrazy — Thu, 03 Dec 2020 12:04:53 GMT

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

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 🙂

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

wdx223_Daniel — Fri, 04 Dec 2020 16:35:15 GMT

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

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

BICrazy — Mon, 07 Dec 2020 06:27:38 GMT

wdx223_Daniel - Worked like a bomb. Thank you

topic Re: Total to Add Up Measure 'Column' Instead of 'Row Formula' in DAX Commands and Tips

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

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

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