Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Leaving a Total on Line and Bar Chart

Currently, I have a chart that looks like this:

 

 

full graph.PNG

 

 

 

I want it to look more like this:

desired graph.PNG

 

 

 

So is there a way to, if I filter out the Q1 weeks, to display a total for Q1 before the Q2 weeks are displayed? So I can see the values that coorespond to only 201913? Becuase right now if I filter that graph to show just 201913 and the whole of Q2, the 201913 value drops down to it's base value, not the cumulative number.

 

The values on the chart are a running total DAX quick measue.

 

Thanks!

6 REPLIES 6
Cmcmahan
Resident Rockstar
Resident Rockstar

When you click the quick measure in the Fields pane to the right, what is the formula that appears at the top?  We should be able to slightly update that expression to get the sum for 201913 while still having the rest of Q1 filtered out of the visual

Anonymous
Not applicable

Here you go @Cmcmahan 

 

Weekly Forecast running total in Year Week Formatted =
CALCULATE(
    SUM('Weekly Forecast'[Weekly Forecast]),
    FILTER(
        ALLSELECTED('Master Date'[Year Week Formatted]),
        ISONORAFTER('Master Date'[Year Week Formatted], MAX('Master Date'[Year Week Formatted]), DESC)
    )
)
 
 
I'll also need this for 20XX26, 20XX39, and 20XX52.
 
So basically the least amount of hard coding the better.
 
Thanks for helping

Sure.  Do you have some indicator in your data of what the quarter is for any given YearWeek?

 

Also, I'm pretty sure your current running total line isn't actually working. It looks like it's just the same as the current value of the Forecast instead of being cumulative.   

 

Should each of the columns be cumulative to all the other columns, or just the line itself?

Anonymous
Not applicable

Each of the weeks is related to a quarter. 20XX01 to 20XX13 cooresponds to a Q1 and so on.

 

The values are largely correct. There is a lot of indivual vlaues behind the calulations. That black line is slightly different than forecast. Forecast is adjusted on a weekly basis while the black line is a one time calulation at the beginning of each year. So they differ by only a few hundred each month (closer together ndicated increased accuracy)

 

Each column should be the value for the current week, plus the last X amount in the quarter. The only one I'd like to change is the last of each quarter. So I can see the total of the last quarter and the weekly cumulative of the current quarter

Nevermind, I kept looking at it and realized that both orange and blue columns as well as the line are all cumulative values.  

I've actually got no idea how the current bars/lines know to reset at the beginning of Q2 with the measure you shared with us.  I was able to replicate the behavior you want with this:

CumulativeAmountSum = 
CALCULATE(
	SUM('Table4'[Amount]),
	FILTER(
		ALL('Table4'),
		ISONORAFTER('Table4'[Time], MAX('Table4'[Time]), DESC) && 
[Quarter]=MAX(Table4[Quarter]) ) )

This will give you a quarter's cumulative value for each date you show, so setting up your visual to show the last week of any quarter will give you the cumulative total from that quarter.

Anonymous
Not applicable

full graph no filter.PNG

 

 

 

So I actually have a filter on there by Quarter. This is what it looks like without the filter. I also have no clue why it resets at the new quarter but I am quite happy it does

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.