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

[JohnYEG]

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. 

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,

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[JohnYEG]

Thanks Ashish! that works!

[Ashish_Mathur]

You are welcome.

[JamesFr06]

You are welcome JohnYEG

[Fowmy]

@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) 
)

[HenriqueReis]

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!

[JohnYEG]

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?

[HenriqueReis]

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

[JohnYEG]

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.

[JamesFr06]

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

[JohnYEG]

Thank you so much!!