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
AmandaSoborg
Helper I
Helper I

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;

Raw data from excel (it is orginialy from SalesForce)Raw 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;

Measure value pr dayMeasure 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;

Value pr monthValue pr month

 

Value pr quarterValue 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 🙂

 

4 REPLIES 4
v-sihou-msft
Employee
Employee

@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,

 

 

@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

Phil_Seamark
Employee
Employee

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!

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 🙂

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.