Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating a measure from two other measures

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.  

Need Help
Tips and Tricks

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.