Select many values in a filter and assign a specific number for calculation depending on the values

[elatreille]

Hi everyone,

I have a filter with 3 values in it (Text value)

728 x 90

300 x 250

640 x 480v

When selecting them, I want to calculate the total revenues but the values doesn't have the same price.

Example

728 x 90 = 1.50$

300 x 250 = 1.50$

640 x 480v = 14$

My formula (with error)

Total rev = IF('Creative Size'[Creative Size]="640 x 480v",([UnSold Impressions]/1000)*14.50,([UnSold Impressions]/1000)*1.50)

The error returns:

A single value for column 'Creative Size' in table 'Creative Size' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

If I am adding SUM():

Total rev = IF(SUM('Creative Size'[Creative Size])="640 x 480v",([UnSold Impressions]/1000)*14.50,([UnSold Impressions]/1000)*1.50)

The error returns:

The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String

After testing Exact, and other functions, I am stock with that 😞

Can someone help me out 🙂

Thanks

Eric

[CahabaData]

Please supply a few representative lines/rows of your table.  The structure is not clear from your post as to what values are in rows and what values are aggregates.  That determines the correct DAX formula.

[elatreille]

Is that helping?

The pricing Is hardcoded in my function... I should probably not do so... I should probably had a field in my creative table with the pricing but at one point it will do the same where depending on the format I am choosing, the price will change.

Here is the visual

18.6 is calculated with the 1.50 I hardcoded in the function, the 640 x 480v should be at 14$ so in reality I should have a lot more than 18.6 in revenue.

Hope that help

Eric

[CahabaData]

Is there a revenue per row/record established? It looks like this is the most granular point of where revenue must be calculated.  Not sure though - just getting an understanding of the data model.....

[elatreille]

No, there is a total of impressions by format (values in the filter)

Another example:

If I select only 300 x 250 in my filter, my function will be:

Total rev = ([UnSold Impressions]/1000)*1.50

If I select only 640 x 480v in my filter, my function will be:

Total rev = ([UnSold Impressions]/1000)*14

If I select both, it needs to take all the unsold impressions for the 300 x 250 / 1000 and multiplies by 1.50 and do the same for the 600 x 480v and return the total.

Does that make sense?

Thanks

Eric

[elatreille]

CORRECTION

No, there is a total of impressions by format (values in the filter)

Another example:

If I select only 300 x 250 in my filter, my function will be:

Total rev = ([UnSold Impressions]/1000)*1.50

If I select only 640 x 480v in my filter, my function will be:

Total rev = ([UnSold Impressions]/1000)*14

If I select both, it needs to take all the unsold impressions for the 300 x 250 / 1000 and multiplies by 1.50 and do the same for the 600 x 480v / 1000 BUT MULTIPLIES BY 14 and return the total OF BOTH.

Does that make sense?

Thanks

Eric

[CahabaData]

well from what I see there are many records of the same Creative Size.  and so I believe you need to create the revenue per row using the appropriate formula for the Creative Size.  then the visuals will aggregate that row Revenue into an overall revenue per Creative Size if that is your goal.