Forum Discussion
Slicers Don't Work
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!
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.
3 Replies
- edhans
Community Champion
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.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
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.
