Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
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.
Hi,
Share the link from where i can download your PBI file.
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |