Forum Discussion

hacksign's avatar
hacksign
Regular Visitor
10 years ago

Formula Problem : IFHASONEVALUE, VALUES, CALCULATING SPEND WITH DIFFERENT TYPES OF COSTS

Hi everyone, I hope somebody can help me with this, and I hope everyone is good.   I am a digital marketer, self taught DAX user, that has stumbled upon a problem bigger than me.   Situation: ...
Vvelarde's avatar
Vvelarde
Community Champion
10 years ago

The results is a error message or an incorrect value?

  • hacksign's avatar
    hacksign
    Regular Visitor
    10 years ago

    The result for Tenancy is an empty cell, while for the other types of costs, it works perfectly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      A bit hard to say with what is given.  I would break this out a bit just to help debug.

       

      Maybe define a measure for AVERAGE('Media Plan'[Costing]) and throw that + [FT Impressions Delivered] broken out by [Cost Method] into a table... just to see if the values are "what you expect" ?

       

      You measure generally looked fine to me, modulo lots of parens and zeros :)

       

       

       

       

      =IF (HASONEVALUE ( 'Media Plan'[Cost Method] ), 
       IF (VALUES ( 'Media Plan'[Cost Method] ) = "Tenancy" && [FT Impressions Delivered] > 500,
            AVERAGE ( 'Media Plan'[Costing]),
           0
      )
)

       

       

       

      • hacksign's avatar
        hacksign
        Regular Visitor
        10 years ago

        Anonymous hah, yes, the parenthesis part is a bit too much. I will try to put the Average in a measure

         

        Do you or Vvelarde have any idea on how to solve the second part of the puzzle, that is, make sure that it calculates everything on a weekly basis? What I think it would have to do, is some kind of SUMX that calculates every item every seven days from the start of the calendar? Or is there any way I can use the Week Number?