# PowerBI: Sum and Count in connection with slicer



## MrAustria (Jan 25, 2021)

Hello,
I am new in this forum and I want to participate to find solutions for any user problems. I am an expert in MS Excel, but just a starter in PowerBI.

Right now I am working on a dashboard for price reductions and I want to make a sum of different types fo price reductions. My table looks like:

_Reduction type / Price reduction / Year_
X1 / -10 / 2019
X1 / -5 / 2020
X2 / -4 / 2020
X3 / 0 / 2019
X3 / -3 / 2019
X3 / -5 / 2019

The result should look like (in total)
_Reduction type / Sum / Number_
X1 / -15 / 2
X2 / -4 / 1
X3 / -8 /2

Furthermore, I would like to use a slicer for the selection of any year. 

Up to now I have the following solution

Calculation of the sum by using the values, slicer works properly (when changing the years, I get values for the years / when not selecting any years, I get the sum)
Implementation of the slicer

For calculating the number I tried two solutions:

Values "Count": However, this counts also if there is no price reduction (= price reduction: 0). Slicer works correctly indeed.
New Column: This counts just entries with price reductions <0, BUT slicer doesn't work. Formula: CALCULATE(COUNT('Table1(Orders)'[PriceReduction]),'Table1(Orders)'[PriceReduction]<0)

How could I solve this problem? I have already tried several things, but it didn't work so far....
The result should be a Table as I showed above with totals for each reduction type and a slicer for selecting different years.

THANK YOU!


----------



## Misca (Jan 25, 2021)

Welcome to the board!

Try something like:

```
Reduction Sum :=
SUM ( Reductions[Price Reduction] )

Reduction Count :=
COUNTROWS ( FILTER ( Reductions; Reductions[Price Reduction] <> 0 ) )
```
You're going to need to change the table names to match the ones in your data model.

Also, never mind the "Power Query" bit: This is DAX.


----------



## MrAustria (Jan 25, 2021)

Hi Misca, thanks for your answer!

However, it doesn't yet work properly as I expect. Maybe I should give you more information about my problem.

My table doesn't just have the price reductions, it has all of the sell items (in general of course positive values = S, price reductions are negative):
_type / Price / Year_
X1 / -10 / 2019
X1 / -5 / 2020
X2 / -4 / 2020
X3 / 0 / 2019
X3 / -3 / 2019
X3 / -5 / 2019
S / 50 / 2019
S / 30 / 2020
S / 40 / 2019
S / 25 / 2020

The sum works properly with using the values of PowerBI: Sum of Price with Price < 0. With that solution also the slicer works. Or is here also a new column better?

However, your given formula for counting the negative prices doesn't work with a slicer. Is there any other solution?


----------



## Misca (Jan 25, 2021)

What do you have in your slicer? And what are you expecting the result to be when you slice that table with a value from it?

Also, it would make it easier for us to help you with your problem if you could tell us more about your whole data model.


----------



## MrAustria (Jan 26, 2021)

*Table "Sales"*
S = Sales
X. = Price reduction

TypePriceYearX1-102019X1-52020X2-42020X302019X3-32019X3-52019S502019S302020S402019S252020

*Table "Price codes"*

TypeDescriptionX1General discountX2Annual discountX3Christmas discount

*Result: without using slicer*

TypeDescriptionSum of reductionCountAverage (Sum / Count)X1General discount-152-7,5X2Annual discount-42-2X3Christmas discount-82-4

If not using a slicer, it is working -> so the total per Type is correctly

*Slicer*
Year: Selection between 2019, 2020 or total

*Result: when using slicer for 2020*

TypeDescriptionSum of reductionCountAverage (Sum / Count)X1General discount-51-5X2Annual discount-41-4X3Christmas discount000

When working with the slicer and selecting any year, I just get the values for total (not the selected year)


In a nutshell, I think that my problems are based on DAX for "sum of reduction", "count" and "Average".


----------



## MrAustria (Jan 27, 2021)

One more detailed question (independent of the slicer): How can I create a DAX that the final table doesn't show type X3 and the total sum is then just X1+X2?


----------



## Misca (Jan 27, 2021)

These measures should work even with the slicers.


```
Reduction Sum:=CALCULATE(sum(Sales[Price]),Sales[Price]<0)

Reduction Count:=COUNTROWS(FILTER(Sales,Sales[Price]<=0))

Avg Reduction:=CALCULATE(AVERAGE(Sales[Price]),Sales[Price]<=0)

X1+X2:=CALCULATE(sum(Sales[Price]),FILTER(PriceCodes,PriceCodes[Type]="X1"||PriceCodes[Type]="X2"))
```

I created 3 tables to my data model. PriceCodes is connected to the Sales using the Type column. Years has only one column and is used in the slicer. Just make sure the connections go from dimensions ( = one side) to the facts ( = many side).


----------

