Forum Discussion

Peter_Ronaldon's avatar
Peter_Ronaldon
Frequent Visitor
2 years ago
Solved

Need Help - PowerBI Measure

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! 

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

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

  • Peter_Ronaldon's avatar
    Peter_Ronaldon
    Frequent Visitor
    2 years ago

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