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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Problem regarding cumulative budget using slicer

I am working on creating a PnL report where I’m showing PnL against budget per month in a bar chart and cumulative PnL against budget in another line chart (separated lines). User has two options by filtering by year and month.

By filtering by month (for example, January - June) I want the result to show for filtered months. But I want the budget graph to show for all 12 months regardless of the month filter. I managed to fix this with in the bar graph, but I’m having problems creating the correct measure showing cumulative budget that exclude the month filter.

Working fine i barchart:

Budget amount = CALCULATE([Budget amount];ALL(Dim_Time[Month]))

Issue in linechart:

Budget accumulated = TOTALYTD(Fact_Budget[Budget amount]; Dim_Time[Date] ; ALL('Dim_Time'))
 

Hope someone can give me an example of measure showing accumulated numbers with filters. Now I have tried everything.

 

 

2019-06-17_22-25-11.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for your response, but it doesn't cuite answer my question.

Luckily i managed to work around the issue by adding some parameters in the query.

 

Issue is solved.

 

 

View solution in original post

2 REPLIES 2

Most people would know how to fix this in Excel. In fact if you switch the line chart to a matrix, you would probably immediately know what to do. Just wrap the formula in an if statement.  Check if here are sales this year, and only show the cumulative  total if there are (or what ever it is you are trying to do). It can be a bit more challenging if you data is loaded daily. In that case you need a different approach. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Thank you for your response, but it doesn't cuite answer my question.

Luckily i managed to work around the issue by adding some parameters in the query.

 

Issue is solved.

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors