Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure with CALCULATE expression throwing wrong totals

Hello community, how are you?

 

I have the following situation:

 

The company have products stored in different warehouses. What we want to know is the following three quantities:

 

Current stock, Last Unit Price, Inventory valuation (that is, the sum of the product of last unit price and quantity of units)

 

We want to represent this in a column with the following structure:

 

Product_Name      |  Current Stock | Last Unit Price | Inventory Valuation |

Blue Paint Bucket                2                      12.000                  24.000

Pencil                                    5                      20.000                 100.000

 

And so on...

 

And we also want to filter this by warehouse using a slicer.

 

The problem is that the totals are wrong. The rows are fine, I know this because I export the data to .csv and make an autosum on Excel, and it shows the correct values. 

 

I was capable of fixing Current Stock and Inventory Valuation totals using SUMX and SUMMARIZE, but the Last Unit Price (LUP) is very difficult. My formula for LUP is:

 

LUP=
var maxdate=CALCULATE(MAX(dm_facts_Stock[date]); ALL(dm_facts_Stock[warehouse]))
VAR ROWS = CALCULATE(SUM(dm_facts_Stock[PRICE])/COUNT(dm_facts_Stock[NROCOMP]); dm_facts_Stock[date]=maxdate)
RETURN
IF(HASONEFILTER((dm_Dim_products[DESCRIPTION])); ROWS;
SUMX(dm_facts_Stock; ROWS))
 
As you can see, I take the total price (PRICE) and divide it by the count of the number of times the product appears on the fact table, so I can have the unit price. Then I tell DAX that the date must be equal to the maxdate variable, which stores the max date a product appears on the facts table ignoring the warehouse filter with the function ALL (because the last time a product appears could be in any of the warehouses). This way it would give me the LAST unit price. The LUP is then stored on the variable ROWS.

 

Lastly, I use HASONEFILTER and IF so that, when it calculates the total, it does so using SUMX, which in theory should solve my problem but does not. It seems that the CALCULATE function creates another context, I suspect this might be related to my problem.

 

PD:. yes, I know the totals are technically right according to the logic of DAX, but this is not the way a table object is supposed to work. I can't imagine a real-life situation where your boss asks for a table that "respects the context of the total row". He would like to see the total for each column, because that is how table totals are supposed to work. There should be an option to override this DAX feature in the case of table objects...in that specific case this is just wrong. It becomes a bug, not a feature, and its very frustrating.

 

Thank you very much in advance.

 

 

 

               

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

Hi @Anonymous ,

Measure formula are calculated based on current row contents, for some calculations which require to get current row content will get wrong value on total level.

Current it not support to calculate with multiple aggregations, you need to add variable to manually summary records and apply second aggregate on summary variable.

You can take a look at following blog to know more about measure total calculation issue:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.