cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Price Movements by sku and supplier (multiple variable) (% change)

Hello,

 

I am looking to track price movements over sku numbers and supplier. I have successfully created a dynamic index that will allow me to find the % change from the previous price while filtering for suppliers BUT I am not succesul in bringing it into a table where it returns the total increase averaged over the sku. I also have well over 1000 skus and supplier.

 

Here is what I have so far in terms of the measure:

 

 

 

average growth % = 
//setting the variables
VAR index = SELECTEDVALUE('Table'[Order])
VAR sku = SELECTEDVALUE('Table'[SKU])

//finding previousindex and previous price
VAR previousindex = CALCULATE(MIN('Table'[Order]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]>index))
VAR previousprice = CALCULATE(SELECTEDVALUE('Table'[ price ]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]=previousindex))

 //Delta % calculations                           
VAR delta = PRODUCTX('Table',1+CALCULATE(
                                (SELECTEDVALUE('Table'[ price ])-previousprice)/previousprice,
                                    FILTER('Table',previousprice>0)
                                        )
                    )-1
RETURN delta

 

 

 

My desired out put would be this where

desired output.JPG1= -6.77%

2= 18.69%

3=  16.94% (AVG(2.88% from 123A & 31% from 456B)

4 = 1.4% (AVG(1.43% from 123A & 1.38% from 456B)

 

Highlight would be 5.96% (Average of 1 & 2)

 

 

 

 

* To Clarify my goal of using a measure instead of a calculated column I want to be able to adjust the index by filters. Therefore if I select just ABC for SKU 123A. The measure will remove orders: 13 &14. This means the % change on order 12 is then

(8.66-7.63)/7.63  = 13.5%. I know I can do calculated columns using earlier function but I will then loose the ability to dynamically calculate the previous price by applying new filters. 

 

SKUSupplierOrderDate price % change1+% changeProduct(1+% change)-1
123AABC12/10/2020                                             7.850.00%100.00%-6.77%
123AABC22/7/2020                                             7.850.00%100.00% 
123AABC31/28/2020                                             7.850.00%100.00% 
123AABC41/20/2020                                             7.850.00%100.00% 
123AABC512/31/2019                                             7.850.00%100.00% 
123AABC611/26/2019                                             7.8520.03%120.03% 
123AABC79/7/2019                                             6.540.00%100.00% 
123AABC87/9/2019                                             6.540.00%100.00% 
123AABC96/29/2019                                             6.540.00%100.00% 
123AABC106/14/2019                                             6.540.00%100.00% 
123AABC115/15/2019                                             6.54-24.48%75.52% 
123AABC123/24/2019                                             8.661.41%101.41% 
123AXYZ133/6/2019                                             8.541.43%101.43% 
123AXYZ1412/6/2018                                             8.4210.35%110.35% 
123AABC1511/16/2018                                             7.63-5.57%94.43% 
123AXYZ1610/26/2018                                             8.08-4.04%95.96% 
123AXYZ1710/16/2018                                             8.42   
456BABC11/31/2020                                          12.005.45%105.45%18.69%
456BABC21/28/2020                                          11.380.00%100.00% 
456BABC31/18/2020                                          11.380.00%100.00% 
456BABC41/10/2020                                          11.383.36%103.36% 
456BABC512/21/2019                                          11.014.86%104.86% 
456BABC611/16/2019                                          10.500.00%100.00% 
456BABC78/28/2019                                          10.500.00%100.00% 
456BABC86/29/2019                                          10.506.60%106.60% 
456BABC96/19/2019                                             9.850.00%100.00% 
456BABC106/4/2019                                             9.850.00%100.00% 
456BABC115/5/2019                                             9.85-5.20%94.80% 
456BABC123/14/2019                                          10.391.37%101.37% 
456BXYZ132/24/2019                                          10.251.38%101.38% 
456BXYZ1411/26/2018                                          10.1110.37%110.37% 
456BABC1511/6/2018                                             9.16-5.47%94.53% 
456BXYZ1610/16/2018                                             9.69-4.15%95.85% 
456BXYZ1710/6/2018                                          10.11   

 

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Price Movements by sku and supplier (multiple variable) (% change)

Having a bit of difficulty grasping this one exactly. But, if you can do this in a calculated column, you can do this in a measure. Just use ADDCOLUMNS and you can use EARLIER within a column that you add. The other trick is to store your table as a variable. Then you create a second table variable using ADDCOLUMNS that uses the first table variable as the first parameter. Then you can do fancy calculations over the original table within your second table.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Price Movements by sku and supplier (multiple variable) (% change)

If you want to use earlier as suggested above by @Greg_Deckler , you can get next or previous price like that

Previous price = maxx(filter(table,table[SKU] = earlier(table[SKU]) && table[Supplier] = earlier(table[Supplier])
							&&  table[order date]  <earlier(table[order date])),table[price])
next price =minx(filter(table,table[SKU] = earlier(table[SKU]) && table[Supplier] = earlier(table[Supplier])
							&&  table[order date]  >earlier(table[order date]),table[price])

 

Based on one of these you can calculate change %. 

 

If you can put up a better example that would help.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Price Movements by sku and supplier (multiple variable) (% change)

The problem with using a calculated column is that I want the results to be dynamic. What I mean by this is that if I start applying filters on the supplier or date, the previous value used in the calculation will update to the next. If I used a calculated column the previous value will remain the same. In the example below, it would mean order # 12's previous value will still be 8.54 even though I have removed this supplier.
no filter.JPGwith filter.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Not sure if I can post a better example but maybe I can explain it better!

 

I want to be able to answer these 4 questions with the data I have and the answers equaling numbers in my original post (red in the screenshot)

1) What is my total % increase on sku 123A

2) What is my total % increase on sku 456B

3) What is my average % increase for supplier ABC

4) What is my average % increase for supplier XYZ

 

However, in practice I may want to be able to go into more detail. Lets say these skus are raw material like Aluminum or steel. My suppliers may also be all around the world (China, US, Europre etc). I may then want to pose the questions which country / region or which material has seen the largest increase. I would want my result to be based on the average of the total % increase of each sku.

 

So my current dax expression does this perfectly on the row level in the tables BUT fails to do so in the totals.  Maybe my header, "average growth" is miss leading abit. 

 

I modified my expression based on what @Greg_Deckler  suggested by using the ADDCOLUMN in the measure but I couldn't get it to work. I haven't used ADDCOLUMN much so maybe I am using it wrong. My results are identical. 

x = 
//setting the variables
VAR index = SELECTEDVALUE('Table'[Order])
VAR sku = SELECTEDVALUE('Table'[SKU])

//finding previousindex and previous price
VAR previousindex = CALCULATE(MIN('Table'[Order]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]>index))
VAR previousprice = CALCULATE(SELECTEDVALUE('Table'[ price ]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]=previousindex))

 //Delta % calculations                           
VAR delta = ADDCOLUMNS('Table',"change",CALCULATE((SELECTEDVALUE('Table'[ price ])-previousprice)/previousprice,FILTER('Table',previousprice>0)))

RETURN AVERAGEX(SUMMARIZE('Table','Table'[SKU],"avg",PRODUCTX(delta,1+[change])-1),[avg])

 

If you need any more clarification, please let me know and I would be happy to explain further!

 

Thanks for the help!

Highlighted
Frequent Visitor

Re: Price Movements by sku and supplier (multiple variable) (% change)

Here is a screenshot if I compare it to a % change from a calculated column. You will notice on order 12 it does not show the correct % change. It still uses XYZ which was filtered out for the purpose of illustrating this. The total I want on the bottom for this which is just SKU = 123A & supplier ABC is 2.88%

which is = PRODUCT ( 1+%change ) -1 OR = (1+.135) * (1+(-.2448)) * (1 + .2003) * (1 + 0) .... * (1+0)with calculated column.JPG

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors