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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.