Forum Discussion
Totals not adding up correctly
Hi,
Due to missing of data in a stock analysis (certain dates are missing, because only the transaction dates are mentioned in the data), I have created a dax formula to calculate the stock levels at the beginning of each month. Unfortunately, this means that there are huge gaps in my data (for example, data for april 2021 and then 6 months no data untill a new data record of november 2021). I try to fill these gaps with a dax formula which is expressed below. This change to the beginning of the month results in a table that looks like this (very simplified version, there are more than 20k different article numbers and dates vary from 1-1-2021 to 31-12-2024):
| Articlenumber | Date | Economic stock recalculated | First day only | |
1 | 1-1-2021 | 10 | 1-1-2021 | |
1 | 1-3-2021 | 30 | 1-3-2021 | |
1 | 4-3-2021 | 50 | ||
2 | 1-1-2021 | 1000 | 1-1-2021 | |
2 | 1-3-2021 | 900 | 1-3-2021 | |
2 | 4-3-2021 | 700 | ||
3 | 1-1-2021 | 30 | 1-1-2021 | |
3 | 1-3-2021 | 10 | 1-3-2021 | |
3 | 4-3-2021 | 60 | ||
3 | 1-8-2021 | 80 | 1-8-2021
|
Then I have added a new date table including the range of first days of each month from 1-1-2021 untill 31-12-2024. This allows me to build a new visual with the stock per first day of the month (adding the date table column to the X-Axis).
To calculate the stock for previous values that are not in the data table, I use the following dax:
Economic stock visual = VAR d =
SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE(
LASTNONBLANKVALUE('Table1 (4)'[First day only], SUM('Table1 (4)'[Economic stock recalculated])),
'Table1 (4)'[First day only] <= d,
CROSSFILTER ( 'Date'[Date], 'Table1 (4)'[First day only], None)
)
All goes well and when I look at the data in a matrix visual it all looks good if I use 1 articlenumber. However, when I add more article numbers and look at the totals and an Area Visual everything goes wrong. It shows the wrong totals and doesn't show me all the different. herewith a screenshot:
Does somebody know how to get the right totals and thus create a right area chart which adds up the different stocks correctly?
Thanks in advance.
3 Replies
Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Greg_Deckler Thanks for your prompt reply. I've tried both options, however there still seems some problems. Could they arise out of the fact that this dax formula I mentioned in my original post actually adds fictive data points whereas normaly these solutions work only for filtered datasets?
Here is the dax I tried to work with:Economic stock visual total = var _table = SUMMARIZE('Table1 (4)','Date'[Date], "Economic stock recalculated", [Economic stock visual]) RETURN IF(HASONEVALUE('Date'[Date]), [Economic stock visual], SUMX(_table, [Economic stock recalculated]))Hi Anonymous ,
Could you create a sample .pbix file for me to test? And show me the desired result with other ways.
For your information security, please don't contain any sensitive information.
Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
