Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Smeek
Frequent Visitor

Sum of sales price diff vs previous year

Hello,

I am trying to show the effect that sales price changes have in comparison to the sales of the previous year.

 

That means: average of sales price per item and customer of shown period - average sales price per item and customer of previous year  *  quantity sold in shown period.

 

My measure: 

Price change effect = IF(SUMX(VALUES(Data[Item No]),[Avg sales price PY]) =BLANK () && SUMX(VALUES(Data[Item No]),[Avg sales price]) <> BLANK () ,"",SUMX(VALUES('Data'[Item No]),([Avg sales price] - [Avg sales price PY])*[Quantities]))

 

It works fine on line level, but the total is incorrect. 

 

The matrix should look like this (sample of April 2022), but with a correct total:

Smeek_0-1679495708883.png

 

 

The left column shows the item numbers and the customers. 

In the last column I have tried to calculate the price effect, but it does not work on the total sum. I need it to sum up the amounts shown, so the total should be 3.551 and not 7.855.

 

I have also measures for the average sales prices:

Avg sales price = DIVIDE(Sum(Tabelle[Sales Amount]),Sum(Tabelle[Quantity]))
Avg sales price PY = CALCULATE([Avg sales price],PREVIOUSYEAR(Kalender[Date]))

 

I have prepared a dummy table, since I unfortuntely cannot load a pbix file to a location outside my organisation. 

Posting DateCustomerItem NoQuantitySales Amount
11.01.2021JimIN-1141014.000
15.01.2021DaveIN-15311.028
20.01.2021JimIN-175201.980
28.01.2021DaveIN-16923.428
01.02.2021KateIN-11421.792
04.02.2021JimIN-175424.158
12.02.2021JimIN-1141014.000
24.02.2021KateIN-11421.792
01.03.2021DaveIN-175302.970
02.03.2021JimIN-17510990
22.03.2021JoeIN-1281834
22.03.2021JoeIN-13822.200
29.03.2021JoeIN-11421.792
29.03.2021JoeIN-1752198
23.04.2021KateIN-11465.376
30.04.2021DaveIN-11421.792
21.05.2021JimIN-11434.200
21.05.2021JimIN-1753297
21.05.2021JoeIN-13815.000
18.06.2021DaveIN-1141210.752
21.06.2021JimIN-1756594
24.06.2021JimIN-11868.850
28.06.2021KateIN-11454.480
28.06.2021KateIN-11811.475
28.06.2021KateIN-17510990
29.06.2021JoeIN-11421.792
12.07.2021DaveIN-11432.688
12.07.2021DaveIN-15333.084
12.07.2021JimIN-1759891
14.07.2021JimIN-11422.800
14.07.2021JimIN-1757693
23.07.2021DaveIN-16611.729
23.07.2021DaveIN-16712.165
26.07.2021KateIN-11454.480
02.08.2021JoeIN-13816.500
03.08.2021JimIN-1758792
10.08.2021JimIN-11434.200
23.08.2021DaveIN-16923.428
26.08.2021JimIN-1755495
09.09.2021JimIN-11434.200
09.09.2021JimIN-17510990
13.09.2021DaveIN-16923.428
13.09.2021KateIN-11421.792
13.09.2021KateIN-1752198
28.09.2021KateIN-11465.376
04.10.2021JoeIN-15311.028
20.10.2021DaveIN-16923.428
20.10.2021JimIN-11457.000
20.10.2021JimIN-17510990
08.11.2021JimIN-11422.800
08.11.2021JimIN-1752198
19.11.2021JimIN-11411.400
19.11.2021JimIN-1756594
06.12.2021JoeIN-13816.500
06.12.2021JoeIN-15322.056
10.12.2021DaveIN-16900
13.12.2021JimIN-11411.400
13.12.2021JimIN-1759891
11.01.2022DaveIN-11421.792
12.01.2022JimIN-11457.500
12.01.2022JimIN-1757693
21.04.2022DaveIN-11311.103
21.01.2022JimIN-11434.500
21.01.2022JimIN-1752198
25.01.2022JimIN-11411.500
25.01.2022JimIN-175282.772
31.01.2022JoeIN-15311.028
04.04.2022DrakeIN-17111.386
08.02.2022DaveIN-175302.970
17.02.2022JoeIN-1141896
17.02.2022JoeIN-138213.000
24.02.2022JimIN-11434.500
24.02.2022JimIN-1759891
09.03.2022JimIN-11434.500
09.03.2022JimIN-1756594
16.03.2022JimIN-11423.000
16.03.2022JimIN-175121.198
30.03.2022JimIN-11457.500
01.04.2022JimIN-11423.000
01.04.2022JimIN-17500
05.04.2022KateIN-114108.960
05.04.2022KateIN-11811.475
08.04.2022DaveIN-16946.856
11.04.2022JoeIN-13817.321
11.04.2022JoeIN-15311.028
11.04.2022AndyIN-12812.501
11.04.2022AndyIN-14521.858
14.04.2022JimIN-175353.535

I hope that someone can point me in the rigth direction. 

Thanks for your help.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Smeek 

please try

Price change effect =
SUMX (
VALUES ( Data[Item No] ),
VAR AvgTY = [Avg sales price]
VAR AvgPY = [Avg sales price PY]
VAR Qty = [Quantities]
RETURN
IF ( AvgPY = BLANK () && AvgTY <> BLANK (), BLANK (), ( AvgTY - AvgPY ) * Qty )
)

View solution in original post

2 REPLIES 2
Smeek
Frequent Visitor

Super, thanks, that works like a charm! Thanks for sorting out my mess! 😃

tamerj1
Super User
Super User

Hi @Smeek 

please try

Price change effect =
SUMX (
VALUES ( Data[Item No] ),
VAR AvgTY = [Avg sales price]
VAR AvgPY = [Avg sales price PY]
VAR Qty = [Quantities]
RETURN
IF ( AvgPY = BLANK () && AvgTY <> BLANK (), BLANK (), ( AvgTY - AvgPY ) * Qty )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors