Calculation with multiple conditions requiring AND / OR

[Anonymous]

Good morning Masters,

I am looking for some help finding out sales on criteria for the Non-Promo and then I can convert the Promo Sales. 

Total Non- Promo Sales  = CALCULATE([TY Dollars],filter('LOWES Dol Sales', [TY Avg Retail]>200),'FAMILY REFERENCE (quarterly)'[Family] = "Champion") - displays correct value ($18.8 MM)

End the end, I am trying to build something like -

Total Non- Promo Sales Filter = CALCULATE([TY Dollars],filter('LOWES Dol Sales', [TY Avg Retail]>200),'FAMILY REFERENCE (quarterly)'[Family] = "Champion") ||

CALCULATE([TY Dollars],filter('LOWES Dol Sales',[TY Avg Retail]>170), 'FAMILY REFERENCE (quarterly)'[Family]="Edgemere") ||

calculate([TY Dollars], filter('LOWES Dol Sales',[TY Avg Retail]>229), 'FAMILY REFERENCE (quarterly)'[Family]="Clean") -

When I wrote this, my result came on the card as "True".

If I can get values based on family and price point, it would be great. Your thoughts are appreciated as always. 

[jdbuchanan71 · Accepted solution]

If you change your original mesure from || to + does it work like you want?

Total Non- Promo Sales Filter =
CALCULATE (
    [TY Dollars],
    FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 200 ),
    'FAMILY REFERENCE (quarterly)'[Family] = "Champion"
)
+ CALCULATE (
	[TY Dollars],
	FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 170 ),
	'FAMILY REFERENCE (quarterly)'[Family] = "Edgemere"
)
+ CALCULATE (
	[TY Dollars],
	FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 229 ),
	'FAMILY REFERENCE (quarterly)'[Family] = "Clean"
)

[jdbuchanan71]

@Anonymous 

Couldn't you just do the following?

Non Promo $ = [Total Dollar Sales] - [Promo $]

That way if you need to make a modification to the [Promo $] calculation the [Non Promo $] will update at the same time.

[Anonymous]

Hi JD,

Thank you for your reply. But it only works with the Champion if I do it that way. I want to to filter on the other families which have different price points. So I don't know how to combine the filters so that when I select on a different family, it changes the values.

[jdbuchanan71 · Accepted solution]

If you change your original mesure from || to + does it work like you want?

Total Non- Promo Sales Filter =
CALCULATE (
    [TY Dollars],
    FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 200 ),
    'FAMILY REFERENCE (quarterly)'[Family] = "Champion"
)
+ CALCULATE (
	[TY Dollars],
	FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 170 ),
	'FAMILY REFERENCE (quarterly)'[Family] = "Edgemere"
)
+ CALCULATE (
	[TY Dollars],
	FILTER ( 'LOWES Dol Sales', [TY Avg Retail] > 229 ),
	'FAMILY REFERENCE (quarterly)'[Family] = "Clean"
)

[Anonymous · Accepted solution]

JD,

Thank you! It looks like it worked!! Adjusted the Promo formula to use the "less than" price point on those three. Now I can add the other families in with their price points for both measure! Have an awesome weekend!