Forum Discussion

Pablo_Ross's avatar
Pablo_Ross
Frequent Visitor
1 year ago
Solved

Measure Help Needed - Totals Sum

Hi, 

 

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). I have read some information about why this is occuring but I havent had any luck in actually resolving.

 

 

 

Can someone please assist me with how I would go about adjusting my measure to resolve this? I appreciate it greatly.

 

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 have included some real data here: https://docs.google.com/spreadsheets/d/1nbXUvosvvRECTovXmcoegJo03CXssq4MsPfo2HZ893I/edit?gid=2100307...

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.

 

 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.
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    Pablo_Ross 

     

    I agree with MNedix , you could have used switch statement instead of nested IF. 

     

    to correct your totals, you need to use an iterator function like SUMX. Like this

    Priv Home O2 Price Impact =

SUMX( <Put your fact table name>,
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
)
)
))
 
)

     

    Need a Power BI Consultation? Hire me on Upwork

     

     

     

    Connect on LinkedIn

     

     

     








    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

2 Replies

  • MNedix's avatar
    MNedix
    Solution Sage
    1 year ago

    Heya,

    The screenshot is very small, I can't make anything out of it. However, as a rule of thumb, I try to stay away from nested IF function (I always make a mistake in the nesting). Try using SWITCH instead, it is more flexible and clearer.

     

    If it solved your problem then please mark it as the solution so others can see it.

     

    Best,

     

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    1 year ago

    Pablo_Ross 

     

    I agree with MNedix , you could have used switch statement instead of nested IF. 

     

    to correct your totals, you need to use an iterator function like SUMX. Like this

    Priv Home O2 Price Impact =

SUMX( <Put your fact table name>,
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
)
)
))
 
)

     

    Need a Power BI Consultation? Hire me on Upwork

     

     

     

    Connect on LinkedIn

     

     

     








    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!