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: I have different types of costs methods that I use to calculate spend.

 

Action: I created a measure that looks at the cost method attributed to a particular item, and calculcates accordingly the correct spend (see below).

 

Problem : Everything seems to work perfectly except for "Tenancy".

 

What I would like to achieve : If Media Plan says that the item cost method is "Tenancy", and if, the number of [FT IMPRESSIONS DELIVERED] for that item is bigger than 500 impressions per individual week, then return the Average of the Media Plan Costing for that amount. If it's less than 500 impressions, then give me a return 0.

 

i.e.

 

  • Item 1, week starting 01/08, 2000 impressions, then $10
  • Item 1, week starting 08/08, 400 impressions, then $0
  • Item 1, week starting 15/08, 30000 impressions, then $10

Total = 20$

 

Something seems not to be working.

 

I tried using IF(AND, but to no avail. Also, how can I add the "number of impressions for that week" is bigger than 500 ?

 

 

 

=IF(HASONEVALUE('Media Plan'[Cost Method]),
if(VALUES('Media Plan'[Cost Method]) = "CPV", [Video Start (YES SUMX)]*AVERAGE('Media Plan'[Costing]),

IF(HASONEVALUE('Media Plan'[Cost Method]),
if(VALUES('Media Plan'[Cost Method]) = "VAD", blank(),

IF(HASONEVALUE('Media Plan'[Cost Method]),
if(VALUES('Media Plan'[Cost Method]) = "CPCV", [FT Video Completes]*AVERAGE('Media Plan'[Costing]),

IF(HASONEVALUE('Media Plan'[Cost Method]),
if(VALUES('Media Plan'[Cost Method]) = "CPM", [FT Impressions Delivered]*AVERAGE('Media Plan'[Costing])/1000),

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

 

 

6 Replies

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