PowerBI: Sum and Count in connection with slicer

[MrAustria]

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:

1. Values "Count": However, this counts also if there is no price reduction (= price reduction: 0). Slicer works correctly indeed.
2. 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]

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]

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]

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]

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

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

Table "Price codes"

Type	Description
X1	General discount
X2	Annual discount
X3	Christmas discount

Result: without using slicer

Type	Description	Sum of reduction	Count	Average (Sum / Count)
X1	General discount	-15	2	-7,5
X2	Annual discount	-4	2	-2
X3	Christmas discount	-8	2	-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

Type	Description	Sum of reduction	Count	Average (Sum / Count)
X1	General discount	-5	1	-5
X2	Annual discount	-4	1	-4
X3	Christmas discount	0	0	0

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]

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]

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).