cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhjo123
Regular Visitor

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):

ArticlenumberDate Economic stock recalculatedFirst day only

1

1-1-2021 101-1-2021

1

1-3-2021 301-3-2021

1

4-3-2021 50 

2

1-1-2021 10001-1-2021

2

1-3-2021 9001-3-2021

2

4-3-2021 700 

3

1-1-2021 301-1-2021

3

1-3-2021 101-3-2021

3

4-3-2021 60 

3

1-8-2021 801-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:

nhjo123_0-1628001173673.png

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 3
nhjo123
Regular Visitor

@Greg_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]))
Icey
Community Support
Community Support

Hi @nhjo123 ,

 

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.

Greg_Deckler
Super User
Super User

@nhjo123 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/547907


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!