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

The 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.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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