Forum Discussion

Peter_Ronaldon's avatar
Peter_Ronaldon
Frequent Visitor
2 years ago
Solved

PowerBI Calculation Help Needed

Hi!   I have the following formula in Excel that I am trying to write into PowerBI to calculate the price impact (price change) between two sales figures (Last years LY Sales and Current Year CY Sa...
Need Help
  • Shravan133's avatar
    Shravan133
    2 years ago

    Try using variables and process your data in batches:

    Price Impact =
    VAR LY_Sales = SUM('YourTable'[LY Sales])
    VAR CY_Sales = SUM('YourTable'[CY Sales])
    VAR LY_Quantity = SUM('YourTable'[LY Quantity])
    VAR CY_Quantity = SUM('YourTable'[CY Quantity])
    VAR LY_Price_Per_Unit = IF(LY_Quantity > 0, LY_Sales / LY_Quantity, BLANK())
    VAR CY_Price_Per_Unit = IF(CY_Quantity > 0, CY_Sales / CY_Quantity, BLANK())

    RETURN
    IF(
    ISBLANK(LY_Sales) && ISBLANK(CY_Sales),
    0,
    IF(
    ISBLANK(LY_Sales) || LY_Quantity = 0,
    IF(
    NOT ISBLANK(CY_Price_Per_Unit),
    CY_Price_Per_Unit * LY_Quantity,
    0
    ),
    IF(
    ISBLANK(CY_Sales) || CY_Quantity = 0,
    IF(
    NOT ISBLANK(LY_Price_Per_Unit),
    LY_Price_Per_Unit * LY_Quantity,
    0
    ),
    (CY_Price_Per_Unit - LY_Price_Per_Unit) * LY_Quantity
    )
    )
    )

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi Peter_Ronaldon - Can you please check the below calculations with if & and conditions 

PriceImpact =
IF (
AND (
[LY Quantity] > 0,
[CY Quantity] > 0
),
(
([CY Sales] / [CY Quantity]) - ([LY Sales] / [LY Quantity])
) * [LY Quantity],
IF (
AND (
[LY Sales] = 0,
[CY Sales] = 0
),
0,
IF (
[CY Quantity] = 0 || [LY Quantity] = 0,
[CY Sales] - [LY Sales],
0
)
)
)

 

Hope it works as expected.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!