Sudden peaks in stacked area chart plotting PowerBI visuals

[Anonymous]

I have a doubt regarding stacked area chart plotting in Power BI visuals. I have data set in excel with bug counts in domain Categorizing and Processing and their respective date and time. I have plotted a stacked area chart in excel which looks like follows.

When I try to do it in Power BI, there are unwanted peaks visible at various points which is unnatural as shown below.

The data set for excel chart and power bi chart are exact same as shown below. (each data snip correspond to value in power bi image above)

For example, it is seen from data that for processing value, graph is increase gradually from 143(pic 1) to 230(Pic 3). But I confused why there is sudden spike in graph for value 153(Pic 2). How can I solve this is issue? Is it something related to inbuilt series calculation in excel and sum or count in Power BI? Also do I need to transform my data in Power BI before plotting, which I think won't be required.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

I did reproduce this same graph and I found the cause of the problem.
The reason for the peak in PowerBI is that the same datetime appears multiple times in the sample data and PowerBI adds up the values for the same datetime, in Excel, it seems to only use the value for a particular datetime. This is where the problem arises. In some ways, PowerBI seems to handle the data more rigorously.

All in all it is the sample data that has duplicate datetime that is causing the problem.
Datetime column:

like this:

Edit:

If you want to show the same visual effect as in Excel, you can change the aggregation method of the sum, such as the average. Then you can get almost the same visualization as in Excel.

like this:

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi, @Anonymous 

I can't reproduce your issue. I created a simple sample and it works well for me.

I'm not sure if this is accidental. Have you tried deleting the visual object and adding the field again? Will this issue disappear?

It would be great if you could share a sample data so that I can test it in my environment.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-angzheng-msft ,

please find the Link to the required  file!!!

could you please try to reproduce the same graph?

[mahoneypat]

It looks like in Excel you put datetime on the x axis and in Power BI, you have just Date.  I'm guessing you have Auto Date/Time turned on in your File/Options and your visual is using the Date level of the automatic hierarchy.  In the X Axis field well, pull down the arrow on that column and choose just the DateTime value (not one of the hierarchy values).

Note: you should consider splitting your DateTime column into Date and Time columns and adding a Date table (and maybe a Time table, if time analysis is needed) to your model.

Pat