locked
Price Index in DAX RRS feed

  • Question

  • I have a huge database with sales and volume and I'd like to calculate the price Index variation from periods.

    Bellow is a samll sample table.

    Period Division Product Volume Sales Cost
    201601 Frutas Amora 1000 10000 5000
    201602 Frutas Amora 1200 11000 7150
    201601 Frutas Banana 250 2600 780
    201602 Frutas Banana 260 2400 600
    201601 Ferramentas Martelo 100 1500 750
    201602 Ferramentas Martelo 120 1600 1040
    201601 Ferramentas Pregos 25000 2500 1125
    201602 Ferramentas Pregos 30000 2600 1430

    After loaded to powerpivot datamodel I can create pivotables but I 'd like to create a calculate field to have the price index as bellow:

    201601 201602 Price
    Fruits Qty Sales Cost Price Qty Sales Cost Price Value Index
    Amora 1.000 10.000 5.000 10,00 1.200 11.000 7.150 9,17 -     833 -5,0%
    Banana 250 2.600 780 10,40 260 2.400 600 9,23 -     292 -1,8%
    Martelo 100 1.500 750 15,00 120 1.600 1.040 13,33 -     167 -1,0%
    Pregos 25.000 2.500 1.125 0,10 30.000 2.600 1.430 0,09 -     333 -2,0%
    Grand Total 26.350 16.600 7.655 0,63 31.580 17.600 10.220 0,56 - 1.626 -9,8%

    The price index formula for the value -833 is (9,17-10,00)*1000

    The biggets problem I have found is to get the grand Total calculated correct!

    Thanks in advance.


    Saturday, May 7, 2016 6:29 PM

Answers

All replies