Slicers Don't Work

[mkornel]

Hi all,

I need help to create formula which would be responsive to slicers.

|Date|Store|Prodcut|Qty|

|Feb-1|A|A|10|

|Feb-1|A|B|20|

|Feb-1|A|C|30|

|Feb-1|A|D|40|

|Feb-2|B|A|1|

|Feb-2|B|B|2|

|Feb-2|B|C|3|

|Feb-2|B|D|4|

FORMULA = (SUM(Product A) + SUM(Product B)) / (SUM(Product C) + SUM(Product D))

I have two slicers: Store and Product. Store is working, but Product is not. I guess its because I'm using it as a part of the formula, but I don't know how to fix this.

I would appreciate any help here! Thanks in advance!

[edhans]

Can you explain exactly what it is you are doing? Your formula isn't valid. SUM() requires a column name, and Product A, Product B, etc. are not column names.

Slicers will definitly work if you are using fields in your table as measures. I just need to understand your goal here.

[mkornel]

Sorry for being too vague.

See dataset.

Date	Store	Product	Amount
Feb-1	A	Pen	10
Feb-1	A	Pencil	20
Feb-1	A	Notebook	30
Feb-1	A	Paper	40
Feb-1	B	Pen	10
Feb-1	B	Pencil	20
Feb-1	B	Notebook	30
Feb-1	B	Paper	40

...

EXCEL FORMULA = ( SUMIF(Product,"Pen",Amount) + SUMIF(Product,"Pencil",Amount) ) / ( SUMIF(Product,"Notebook",Amount) + SUMIF(Product,"Paper",Amount) )

GOAL - I want to have two slicers: store and product. Both should affect formula, e.g. unselecting "Pen" should set 0 amount in formula for "Pen"

[edhans · Accepted solution]

Ok. See if this is what you want. See the attached PBIX file. Please note the formula will not work unless you have Pen and/or Pencil selected and Paper and/or Notebook. In other words, if you only select Pen and Pencil, the formula returns nothing because there is no denominator.