Cannot Get a Measure to Sum As Expected

[JDiOrio]

Hello everyone, and thanks in advance for any and all help you can provide. It is very much appreciated!

I’ve been pulling my hair out all day on this one. I’ve tried to research the question, and saw some on-line resources that referenced including HASON Filters and some other fixes. However I can’t seem to get anything to work correctly.

Summary:

I’m trying to calculate Year-Over-Year Price Increases at a transactional level (i.e. Product # per Sales Transaction level), and then be able to aggregate this up for a SalesPerson and Region. I am trying to exclude any Product # that has not had any Sales in the Previous year.

Background:

I have a Sales Table, called (order_history) , as well as a Date Table called (calendardatetable) . I have a large number of Measures built off of these tables, with the pertinent ones being the following and the last one that’s giving me the problem:

List item

Revenue Measures

• Extended Revenue CY (Calendar) = CALCULATE(SUM(order_history[ext_price]))
• Extended Revenue PY (Calendar) = CALCULATE(SUM(order_history[ext_price]),SAMEPERIODLASTYEAR(calendardatetable[Date]))

Price Measures

• Average Price = [Extended Revenue CY (Calendar)]/[Quantity CY (Calendar)]
• Average Price PY (Calendar) = CALCULATE([Extended Revenue CY (Calendar)]/[Quantity CY (Calendar)],SAMEPERIODLASTYEAR(calendardatetable[Date]))
• YoY Average Price = [Average Price] - [Average Price PY (Calendar)]

Quantity Measures

• Quantity CY (Calendar) = CALCULATE(SUM(order_hstory[shp_qty]))
• Quantity PY (Calendar) = CALCULATE(SUM(order_history[shp_qty]),SAMEPERIODLASTYEAR(calendardatetable[date]))

YoY Price Differential

• CY Qty Extended YoY Price Differential = if([Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0)

So basically, what I’m trying to do is Calculate the Price Differential on items that were sold in the Current Year and in the Prior Year, and apply that YoY Average Price to the Current Year Quantity Sold. I don’t want to include in the calculation items that were sold in the current year but not last year (which would yield a large Positive number) or those that were sold last year but with no Sales in the current year (which would yield a large Negative number).

So I’m sure that the experts out there already can guess what is happening: my DAX formula is accurately being applied to both the Rows (which I want it to do) and the total (which I don’t want it to do). In the screenshot below, it’s the 4th column over titled [CY Qty Extended YoY Price Differential] that’s the problem. I need this Total to show -$3,319.22, not the $51,223.40 that it is currently calculating independently of the row results.

Please help!

[EnterpriseDNA]

Hi JDiOrio, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

• Include the masked demo pbix file.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

[Harsh]

Hello @JDiOrio,

Thank You for posting your query onto the Forum.

Firstly, it’s always helpful when working or mock-up PBIX file is provided for the reference so that members of the forum can assist you in a better and efficient manner. Without which it’s just a guess work for the members and consumes their time.

Now, coming back to the solution part. You can use the measure as provided below for the reference -

CY Qty Extended YoY Price Differential =
SUMX(
  SUMMARIZE(
     Order_History , 
     Order_History[customer_description] , 
     calendardatetable[Year] , 
     "@Totals" , 
     [CY Qty Extended YoY Price Differential] ) , 
  [@Totals]
)

I’m also providing a link below which one of our expert @Greg had created onto the forum which specifically addresses this type of issues pertaining to the fixation of the totals.

Hoping you find this useful and helps you in solving the problem that you’re facing.

Note:

1). In the above formula, it’s presumed that “customer_description” field is from the Sales table i.e. “Order_History” table and “Year” is from the Date table i.e. calendardatetable.

2). Please adjust the naming conventions accordingly in your file. I’ve used the naming conventions based on the information provided into the post.

3). To view the entire post pertaining to “Fix Incorrect Totals”, please click onto the link and not onto the “expand/collapse” button.

3). If this doesn’t solve the requirement than please post a working or mock-up PBIX file so that members can assist you further.

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[JDiOrio]

Thanks for the reply @Harsh …very much appreciated! I’m afraid that I wasn’t able to get the above solution to work, however someone else pointed me towards the following, which seemed to work:

  CY Qty Extended YoY Price Differential = SUMX(
                                                    VALUES(
                                                        order_history[product]),
                                                        IF(NOT(ISBLANK([Quantity PY (Calendar)])
                                                        )
&&
                                                        [Quantity PY (Calendar)]>0,[YoY Average Price]*[Quantity CY (Calendar)],0
                                                        )
                                                    )

I’ll have to read in more detail either how to mask the PBIX data prior to sharing or else next time I’ll try to mock something up from a PBIX perspective. I know that would have helped ALOT

Thanks again for the help!

[Harsh]

Hello @JDiOrio,

You’re Welcome.

I’m glad that you got your solution.

I’m marking your post as a solution and closing out this thread.

Thanks and Warm Regards,
Harsh

[JDiOrio]

Ok @Harsh, I may have spoken too soon when I said that the fix that someone else pointed me towards worked. Going back to your solution (shown below), the problem that I’m running into is that Power BI doesn’t seem to let me reference the name of the measure in line 8 that is also the name of the measure that I’m writing the formula for. I’m not sure if I’m explaining that correctly, but do you know what I mean?

CY Qty Extended YoY Price Differential =
SUMX(
  SUMMARIZE(
 Order_History , 
 Order_History[customer_description] , 
 calendardatetable[Year] , 
 "@Totals" , 
 [CY Qty Extended YoY Price Differential] ) , 
  [@Totals]
)

[Harsh]

Hello @JDiOrio,

Firstly, you’re posting the query on a closed thread. I request you to please create a new thread for your query now.

Also as was suggested earlier, that without PBIX file it’ll just be a guess work for the forum members to assist you. Please provide the PBIX file so that forum members can assist you in a better and efficient manner.

Thanks and Warm Regards,
Harsh