Forum Discussion
New User - Bar Chart Keeps Adding Cumulative Total
Hi Everyone,
I am new to Power BI. I am working on my first chart so your support and help is much appreciated. I created Bar chart.
The cart works good when left at quarterly level since that is the raw data. If drilled up to Year, it adds the previous quarters (which are already cumulative) and gives an incorrect number for the year. There is no option in the chart for this number to be as is. Options are like SUM, Average, Max, Mix but none are good as it doesn't actually do the total for that year only. Any idea what to do?
I could not finda way to upload a file so am pasting a link.
https://drive.google.com/file/d/1OEziH4c8C7DS5M6STc4S87Y0NHR7y5iC/view?usp=drive_link
https://drive.google.com/file/d/1OEziH4c8C7DS5M6STc4S87Y0NHR7y5iC/view?usp=drive_link
Screen shot of this is attached here for your reference.
Available options for manipulation are shown below. I do not see No Calculation Option.
Than kyou for your help.
1) The raw data I get it comes in cumulative format.Next time please lead with this crucial piece of information
see attached.
6 Replies
please check the link, it requires access.
Okay. I went and made sure its not restricted. Didn't realize that. I would love to upload the PIBX here but not sure how. Thanks again.
Drop the cumulative column, it is not needed and can be replaced by a (quick) measure.
What you must have is a calendar table. I added that to your data model.
see attached.
Thank you again. I will need to learn a bit more to fully understand what you did. It seems very close, however here are some few additional hicups...
1) The raw data I get it comes in cumulative format. I manually did the QTR Calculation and added that column as I was thinking that there may be some way to use that. The way you did is awesome. I don't fully know the calcluation you used for the measure. it would be nice if you would explan it out here. I will google and try to learn and ask question in case that is preferred.
2) The reason why the current chart is not going to be accurate is that what we have in the QTR column is the DELTA. so the actual Loans receivable is cumulative from 2014 in the raw data. I need to reflect and discuss with management why they want it that way, but for now that's how its being created in the XL. So when we use only DELTA, we are missing about 12M of previously accumulated Loan Receivable Balance. that was the starting number in 2014 balance sheet.
I am not sure if I am making sence. Thanks again for putting this time and effor to help me. Much appreciated.
3) Lastly, I just can't understand why there is no SUM option for using the raw column data from the cumulative.
Thank yo so much lbendlin for your great help. Your solution works. I don't understand the equation so may ask for a favour later, if I can't find a way to learn and understand it.
