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
Esanda
Frequent Visitor

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

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.

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
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.