Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
26 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
25 | |
16 | |
14 | |
13 |