Need Help - PowerBI Measure

[Peter_Ronaldon]

Hi,

I have the following PowerBI Measure:

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 = [LY_Price_Per_Unit]

VAR CY_Price_Per_Unit = [CY_Price_Per_Unit]

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 Price Impact is 0 or blank.

The above formula essentially needs to show me: if Quantity LY exists and Quantity CY exists, what is the difference between Price LY and Price CY.

Can somebody please help me adjust my formula?

Thank you ❤️

Thank you! 

[Peter_Ronaldon]

Firstly, I appreciate the help,

Is there a way that I can stop all of these blank rows showing up when I add this Price Impact Measure?

For instance, there are no sales (PY or CY) and no Quantity (PY or CY), however, a line is still added with this Price Impact Measure returning a value of $0. Is there a way to stop this from happening without simply filtering out blank rows in say column PY Quantity on the visual itself?

Thank you! 😄

[bhanu_gautam · Accepted solution]

@Peter_Ronaldon , Try updated measure

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 = [LY_Price_Per_Unit]
VAR CY_Price_Per_Unit = [CY_Price_Per_Unit]

RETURN
IF(
ISBLANK(CY_Quantity),
0,
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
)
)
)
)