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
JohnYEG
Frequent Visitor

Create a measure from summing from two separate columns (if client occurs in either column)

Looking to see how I can create a measure that is able to track the revenue of a Client if it occurs in either column. 

 

JohnYEG_0-1653679655265.png

 

For example I would like to record the revenue of Client A on whether if they were a billing company or end company.

 

For example, company A was a billling twice ($400) and End Company once ($200)

 

So I would like a measure that shows Company A = $600.

 

Thank you,

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish! that works!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

You are welcome JohnYEG

Fowmy
Super User
Super User

@JohnYEG 

This measure should work for you:

 

Revenue A = 
VAR __CO = "A"
RETURN
CALCULATE(
    SUM(Table[Revenue]),
    (Table[Billing Company] = __CO || Table[End Company] = __CO) 
)

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

HenriqueReis
Resolver I
Resolver I

Hi,

 

You can create measures and manipulate them, like:

 

Measure 1= Calculate(sum(column[revenue]), Billing company = "A")

Measure 2= Calculate(sum(column[revenue]), Billing company = "B")

Measure 3= Calculate(sum(column[revenue]), Billing company = "C")

 

And then make another measures using Measure1, 2 and 3.

 

Regards!

Are you saying that then I would another measure 

Measure 1 End Company A = Calculate(sum(column[revenue]), End Company = "A")

then I would add Measure 1 + Measure 1 End Company A = total revenue of Company A?

 

 

Im not sure if it's what you want, but by  what I understood, it's going to work rsrs

ah okay, for that example company A, I can't just add those 2 columns together. What I would like is how do we add a condition where I don't double count. For example on Contract 3, Company A is both Billing and End but I only want to count it once.

 

JohnYEG_0-1653681768228.png

 

 

Anonymous
Not applicable

Hi try this it sould work

total revenue =
var activproj=SELECTEDVALUE(Billing[end company])
var test=calculate(sum(Billing[revenue]),(Billing[billing company]=activproj))
var test2=if(AND(SELECTEDVALUE(Billing[billing company])<>activproj,SELECTEDVALUE(Billing[end company])=activproj),SUM(Billing[revenue]))


return
test+test2

Thank you so much!!

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.