PowerBI Calculation Help Needed

[Peter_Ronaldon]

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 Sales), if certain conditions are met.

These conditions are essentially:

• If sales is 0 last year and this year, the price impact should be 0 
• If sales is present but quantity is 0, CY Price - LY Price 
• If sales is present and quantity is present, (CY P/Q)- (LY P/Q) X Quantity (LY)

Here is the formula I have been using in Excel:

=(IF(AND(LY Quantity>0,CY Quantity>0),(((CY Sales/CY Quantity)-(LY Sales/CY Quantity)))*LY Quantity,IF(LY Sales=0,0,IF(CY Sales=0,0,CY Sales-LY Sales))))

Can someone please help me with an equivelant formula that I could use in PowerBI to calculate this?

I appreciate the help!!!! ❤️

[Shravan133 · Accepted solution]

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]

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! 

[Peter_Ronaldon]

Thank you for your help!

I'm using quite a large data set and when I implement this measure into my report I am met with the message: "Visual has exceeded the available resources"

I have tried to filter the visual down to include less lines (for instance, only one product instead of the 1000+ available and still getting this message.

Would it be something to do with the measure itself or is this a problem on my end?

[rajendraongole1]

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