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

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?