locked
Trying to find the best approach RRS feed

  • 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

    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

    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 5-10 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.com

    Tuesday, 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

    Tuesday, August 7, 2018 9:20 AM