Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to Solution.
New measure =sumx(values(table[campaign]),[old measure])
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |