Forum Discussion
Formula Problem : IFHASONEVALUE, VALUES, CALCULATING SPEND WITH DIFFERENT TYPES OF COSTS
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 ) )
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?
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.
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)
