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
    )
    )
    )

Shravan133's avatar
Shravan133
Super User
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
)
)
)

Peter_Ronaldon's avatar
Peter_Ronaldon
Frequent Visitor
2 years ago

Thank you for sharing this,

 

I just had one question, I'm not sure if I've gone wrong somewhere but, is there a way to have the Price Impact show as blankif the CY values are blank?

 

For instance:

 

Price Impact =
VAR LY_Sales = ([EXTENDED PRICE SPLY])
VAR CY_Sales = ([Extended Price CY])
VAR LY_Quantity = [BASE QUANTITY SPLY]
VAR CY_Quantity = ([Base Quantity CY])
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
)
)
)
 
This returns the following for Price Impact:

 

I am trying to have it so that if, say Base Quantity CY is blank, then the PriceImpact also = 0.

 

Thank you!