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 item line, however, in the totals, is not summing the individual lines and instead, seems to be executing the measure on the sales and quantity column totals (as shown in the first image below).

 

 

The measure that I am using is here:

 

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 know that this total line is incorrect because I have conducted the same formula equivalent in Excel and am returning this value (in yellow below)

 

I would greatly appreciate any help in tweaking my measure to have resolve this, or if anyone has any information on how to resolve this another way, that would be great!

 

Thank you in advance! 😄




Need Help

2 Replies

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