Creating a measure from two other measures

[Anonymous]

I have a table (Measures) that is composed entirely of measures that are all created from at least two tables. I need to create a new measure, that when used in a matrix, will only calculate when another measure is a non-zero. For example:

Measure1: Cost = sum(table1[cost]) + sum(table2[cost])

Measure 2: Plays = sum(table1[plays]) + sum(table2[plays])

Measure 3: Cost/Play = Divide ( Cost, Plays, 0), but only for items where Plays are > 0 (thus only using the cost for items where plays > 0). This result is needed for correct summing and calculation in a matrix grand total (matrix is split by country and publishers, so publishers without any plays should not be counted in the cost/play totals as well. 

I've tried various versions using SumX, Calculate, and Filter but haven't had any luck.  

[Anonymous]

Thank you @amitchandak  and @mahoneypat  for your suggestions - unfortunately neither seem to have worked. Please see the below matrtix. The actual outcome for cost/plays at the "USA" level ($0.62) should be the same as the "LinkedIn" level $(0.35). I am starting to suspect an issue with the summing and subtotaling (as cost/play for "programmatic should be $0, as no items with plays had any associated spend) but I could be wrong

[Anonymous]

For posterity's sake (and anyone that may care), I seem to have found a solution. 

I created a new measure (Vid_cost) that only sums the spend for items that have video views using Calculate, sumX, and IF:

vid_cost = calculate(sumx(SiteTable, if([Video Plays]>0, [Spend], BLANK())))

I can then adjust the Cost/Plays formula to calculate using the Vid_Cost measure, resulting in a correct total and subtotal at both the publisher and country level. 

[v-xuding-msft]

Hi @Anonymous ,

If it still doesn't work after trying the formulas, please share some sample data and expected output to us. We will understand clearly about your actual situations.

[amitchandak]

@Anonymous , Try 1st

Cost/Play = Divide ( calculate(Cost,filter(table2,[Play]>0)), Plays, 0)

Or Add your dimensions in place of the dim , dim name (Common display) to correct the row context

AverageX(summarize(Dim, Dim[Name], "_1",if([Play]>0,[Cost],blank()),"_2",[Play]),divide([_1],[_2]))

[mahoneypat]

Have you tried an expression like this?

NewMeasure = IF([plays]>0, DIVIDE([cost], [plays],0))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat