Forum Discussion

Pablo_Ross's avatar
Pablo_Ross
Frequent Visitor
1 year ago

Grand Totals Measure Incorrect - Help Needed

Hi All!   I just had a question regarding a measure I am using to essentially show the change in average selling price compared to last year. The measure is working correctly for each individual i...
Need Help
Ritaf1983's avatar
Ritaf1983
Super User
1 year ago

Hi Pablo_Ross 

The issue you're facing with the incorrect grand total is quite common in Power BI. It stems from the fact that the total row doesn’t simply sum all rows above it; instead, it has its own context, which can lead to unexpected results.

To address this, you’ll need to adjust your DAX logic to handle the total row separately.

Here are some video resources that can help with different approaches to fix this issue:
https://www.youtube.com/watch?v=J2FTRqrx0m0&t=10s

https://www.youtube.com/watch?v=YtIdcCYnZ9w&t=1s

https://www.youtube.com/watch?v=6rgAkejrup8&t=1369s
The last two are from SQLBI, and I highly recommend watching them until the end because they really explain the logic behind how it works.
For more specific suggestions 

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly



  • Pablo_Ross's avatar
    Pablo_Ross
    Frequent Visitor
    1 year ago

    Hi, Thank you for your reply Ritaf1983 

     

    I appreciate you sharing these resources with me - While they are quite interesting, I am still struggling to implement something into my measure specifically that will resolve the issue I am having.

     

    I have included some real data here: https://docs.google.com/spreadsheets/d/1nbXUvosvvRECTovXmcoegJo03CXssq4MsPfo2HZ893I/edit?gid=2100307022#gid=2100307022

    Which compares sales and quantity sold from Current Year to Previous Year  (June  to June), specifically for my Private Home O2 data set. While the sales data is actual, I have obviously just randomised the item numbers/customer names etc.

     

    I have also included the expected values in the price impact column - which again, I am having no problem in BI creating the measure for these individual lines (using the below formula):

     

    Priv Home O2 Price Impact =
    VAR LY_Sales = [Priv Home O2 Sales PY]
    VAR CY_Sales = [Priv Home O2 Sales CY]
    VAR LY_Quantity = [Priv Home O2 Quantity PY]
    VAR CY_Quantity = [Priv Home O2 Quantity CY]
    VAR LY_Price_Per_Unit = [Priv Home O2 LY_ASP]
    VAR CY_Price_Per_Unit = [Priv Home O2 CY_ASP]

     

    RETURN
    IF(
    ISBLANK(CY_Quantity),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) && ISBLANK(CY_Sales),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) || LY_Quantity = 0,
    IF(
    NOT ISBLANK(CY_Price_Per_Unit),
    CY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    IF(
    ISBLANK(CY_Sales) || CY_Quantity = 0,
    IF(
    NOT ISBLANK(LY_Price_Per_Unit),
    LY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    (CY_Price_Per_Unit - LY_Price_Per_Unit) * LY_Quantity
    )
    )
    ))

    I have also included (highlighted in yellow) the total I expect using a simple SUM formula - This is the part that I am struggling to replicate in BI.
     
    Please let me know if anyone can assist me with a specific measure to tweaking my existing methodology above to reach this expected total.
     
    I appreciate the assistance as always!
     
    Thank you.