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

Problems with accumulating in stacked column chart

Hi everyone

 

I have a problem with my Power BI report

I want to calculate a running total of my sales, but i am not sure i am doing it right.

This is how my raw Data looks;

value by month.PNGRaw data from excel (it is orginialy from SalesForce)

I have a DimDate table with all dates from 2000 to 2099. These two tabels are connected with a relation 1:*.

 

The total value is the value I will get over a periode of 12 months. Therefore i create a measure to calculate the value i will recive pr day. The measure looks like this;

measue value pr day.PNGMeasure value pr day

 

Then i create another measure, to make the value pr day repete it self from date of first delivery to a specific enddate (31-12-2021). This measure looks like this;

 

valueadded.PNG

I am not even sure that this is the right formular.

But when I plot the Valueadded into a visual with the date from DimDate i see this;

valueadded year.PNG

 

This is pr year, and if I drill down to the month or quarter i see this;

valueaddedmonth.PNGValue pr month

 

valueadded quarter.PNGValue pr quarter

as you can see, the value is the same weather I look at it pr year, quarter or month. And then I also noticed that i have a dip in december 2017 when I look at months. Maybe the measure thinks it should average the value in stead of adding them together? and how do i get the visual to add the values from all four quarters into one year?

 

Please someone help me Smiley Happy

 

4 REPLIES 4
Phil_Seamark Super Contributor
Super Contributor

Re: Problems with accumulating in stacked column chart

Hi @AmandaSoborg

 

Do you want to reset the running total each year?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

AmandaSoborg Regular Visitor
Regular Visitor

Re: Problems with accumulating in stacked column chart

Hi @Phil_Seamark

 

I want the visual to show me the value of the opportunities from the Date of First Delivery and until 2021. So if I have an opportunity with a DOFD on the 1st of january 2018 I want this to continue until the 31st of december 2021. Then i have another opprtunity starting the 31st of january 2018. This opportunity should then be added to the first opportunity on this date.

 

I want the visual to show the calculated value pr day from DOFD to the 31st of December 2021. This also means, that I want the visual so summarize the data when i move up a level. E.g. If I have a value of $100 pr day, in the month of January and I look only at the month of January, I want to see the value of $100 for each day. But if I move up a level and to where I can see all of my months of a year, and not just the days, I want the visual to display $3100 on the month of January ($100*31days=$3100) 

 

Right now I do not wish the data to reset each year Smiley Happy

Moderator v-sihou-msft
Moderator

Re: Problems with accumulating in stacked column chart

@AmandaSoborg

 

 

In this scenario, your "running" measure is based on current date to specify the "up to date" range. So when going to 2018, there's no fact data, then your running total supposes to keep same value all the way to the end. 

 

If you want to keep it in one year, you need to apply filter on month number like: 

 

=
CALCULATE (
    [measure],
    FILTER ( ALLSELECTED ( Table ), DimDate[MonthNum] <= MAX ( DimDate[MonthNum] ) )
)

If you want to calculate the running total within each year, you should use TOTALYTD() function. 

 

Regards,

 

 

AmandaSoborg Regular Visitor
Regular Visitor

Re: Problems with accumulating in stacked column chart

@v-sihou-msft

 

My problem is not limiting the dates to show me a year or a time line Smiley Happy

 

My problem is, that when I look at my data pr year or pr month, the value is the same. Please look at the pictures.Value pr monthThis is my Value pr month

Value pr quarterAnd this is my value pr quarter.

 

As you can see, the value is the same on the y-axis no matter how far I drill down.

 

Each one of my values has a start date (Date of first delivery). If I have a start date on the 1/1-2018, I want the value to show from that day until I tell it to stop. So lets say I have $100 pr day for this specific product. When I am on the day level, I want it to show me the $100 for each day. But if I drill up to the month level, I want the y-axis to display 3100 (31*100=3100). When if i drill up again, so I am on the Quarter level, I want the value to be 9000 (90*100=9000).

 

But right now, the value on the y-axis doesn't change when I drill through the different levels.

So how do I do that? Smiley Very Happy

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 432 members 4,304 guests
Please welcome our newest community members: