HELP IN CALCULATED COLUMN

[VANI_YN_80]

Hi All,

I am a new user of Power BI and stuck in between to get this data. 

I have a data with Current year and previous year where i have a material which is purchased with different vendors ( multiple vendors with multiple purchases). I have to find the Average of each material and compare it with the previous year and to multiply with current year Quantity to get the PPC (purchase price calcuation) PPC formula is APP (average purchase Price) FY - APP PY * FY Quantity.

Can anyone help with me urgently with this calculation ?

Thanks in advance. 

Regards

Vani Y N

[v-eqin-msft · Accepted solution]

Hi @VANI_YN_80 ,

According to my understanding, you want to calculate value based on each material using this calculation :

(average price of this year  - average price of last year) * total quantity of this year

If so, I have built some data for test. Please check:

You could use the following formula to create a column:

PPC Column = 
VAR _appFY =
    CALCULATE (
        AVERAGE ( 'Table'[Price] ),
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
    )
VAR _QuaFY =
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
    )
VAR _appPY =
    CALCULATE (
        AVERAGE ( 'Table'[Price] ),
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        YEAR ( 'Table'[Date] )
            = YEAR ( TODAY () ) - 1
    )
RETURN
    ( _appFY - _appPY ) * _QuaFY

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.