Answered by:
Trying to find the best approach
Question

Hi, firstly I'm very new to Power Pivot, but I've got good experience with excel and pivot tables, and a little experience with DAX.
I have a dataset which gives me the prices paid for a range of products from a number of different suppliers, over time.
What I want to do is compare the price paid, given a specific date, versus the average for the previous period (financial year); given the product and supplier. I may want to select an aggregate of suppliers, so average needs to be weighted across them.
Can anyone help with a suggest approach?
Thanks in advance, James
Monday, August 6, 2018 8:37 AM
Answers

Hi James,
Thanks for your response.
You can just create 3 measures called Units, Value and AvePrice as below DAX formula:
Unit = sum(Sales[SUM_UNITS_FY]) Value = SUM(Sales[GBPVALUE]) AvgPrice = AVERAGE(Sales[Price])
Based on your sample data, the price in the expected results seems not correct, please refer to the results in the screenshot.
Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
 Edited by willson yuanMicrosoft contingent staff Tuesday, August 7, 2018 9:22 AM edit
 Proposed as answer by Ramakrishnan.lh Tuesday, August 7, 2018 9:43 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Friday, April 5, 2019 2:19 AM
Tuesday, August 7, 2018 9:20 AM
All replies

Hi James,
Could you please post some sample data related to your dataset and your expected output?
In this way, we will be able to help you better if you have a sample dataset. Please mask the data if it's sensitive.
HTH,
Ram
Please vote as helpful or mark as answer, if it helps
 Proposed as answer by willson yuanMicrosoft contingent staff Tuesday, August 7, 2018 1:09 AM
 Unproposed as answer by Ed Price  MSFTMicrosoft employee Friday, April 5, 2019 2:18 AM
Monday, August 6, 2018 11:56 AM 
Hi James,
Thanks for posting here.
I agree with Ram, it is pretty hard to answer this without sample data and expected result. To solve your question more efficiently, would you mind typing out 510 rows of example data for these tables, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.
Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.comTuesday, August 7, 2018 1:11 AM 
Hi Willson and Ram, below is some sample data and a sample output. I'd like a pivot table, and eventually a PowerBi dashboard that compares the Price in the "Sample Data" to the Sample Reference "Values". For example, I'd like to compare the price in row 6, to the average price for the same product in 2016 (40.51). I also need the flexibility to group by supplier if needed.
Sample Data:
Product SUPPLIER_GROUP GBPVALUE FISCAL_YEAR SUM_UNITS_FY Price A X 9,042 2015 500 18.08 A X 8,996 2015 600 14.99 A X 8,996 2015 700 12.85 A X 9,076 2015 800 11.35 A X 9,054 2016 900 10.06 A X 9,684 2017 1,000 9.68 A X 9,660 2018 500 19.32 A Y 4,521 2015 600 7.54 A Y 4,498 2016 700 6.43 A Y 13,493 2017 800 16.87 A Y 4,538 2018 900 5.04 A Y 14,491 2015 1,000 14.49 A Y 71,528 2016 500 143.06 A Y 71,528 2017 600 119.21 B Y 71,528 2018 700 102.18 B Y 71,161 2015 800 88.95 B Z 53,370 2016 900 59.30 B Z 53,370 2017 1,000 53.37 B Z 71,161 2018 500 142.32 B Z 71,626 2015 600 119.38 B Z 76,421 2016 700 109.17 B Z 36,131 2017 800 45.16 B Z 19,275 2018 900 21.42 B Z 19,275 2015 1,000 19.28 B Z 19,275 2016 500 38.55 B Z 45,102 2017 600 75.17 B Z 9,020 2018 700 12.89 B Z 54,122 2015 800 67.65 B Z 26,922 2016 900 29.91 B Z 44,870 2017 1,000 44.87 B Z 72,261 2018 500 144.52
Sample Reference Values:
Product Fiscal Year Units Value Price A 2015 4200 55122 13.12 A 2016 2100 85080 40.51 B 2015 3200 216184 67.56 B 2016 3000 175988 58.66 Thanks, James
Tuesday, August 7, 2018 8:46 AM 
Hi James,
Thanks for your response.
You can just create 3 measures called Units, Value and AvePrice as below DAX formula:
Unit = sum(Sales[SUM_UNITS_FY]) Value = SUM(Sales[GBPVALUE]) AvgPrice = AVERAGE(Sales[Price])
Based on your sample data, the price in the expected results seems not correct, please refer to the results in the screenshot.
Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
 Edited by willson yuanMicrosoft contingent staff Tuesday, August 7, 2018 9:22 AM edit
 Proposed as answer by Ramakrishnan.lh Tuesday, August 7, 2018 9:43 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Friday, April 5, 2019 2:19 AM
Tuesday, August 7, 2018 9:20 AM