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

[hacksign]

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)

[Vvelarde]

The results is a error message or an incorrect value?

[hacksign]

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

[Anonymous]

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]

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

[Vvelarde]

@hacksign

1: You can add a calculated column in your table to get the week number:

WeekNumber=WeekNum(Table[Date])

2.Use this in your table visual.

[Anonymous]

Generally, if you are doing fancy date things...  you are going to want a separate date table, and relate it back to your main data table. I wrote this before Power BI, but should still apply... http://tinylizard.com/power-pivot-date-table

Averaging over any TimeUnit is going to use AVERAGEX (well, a SUM() and a divide by the count of time units...)

=AVERAGEX(ALL(Calendar[WeekNum]), [Some Measure])

(iterate over each of the Weeks, evaluating [Some Measure] for each and average the results together)