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

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

4 Replies

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

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

       

  • Peter_Ronaldon's avatar
    Peter_Ronaldon
    Frequent Visitor
    2 years ago

    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?