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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SamWhitehouse00
New Member

Power Bi dynamic filtering of Finance matrix

Hi, 

 

I have the below data structure:

  • Project Table
  • Budget Table
  • Actuals Table
  • FY table (Which is a What If Table)

SamWhitehouse00_0-1660152128690.png

SamWhitehouse00_5-1660152709179.png

 

 

I am trying to create a matrix where, for each project, it shows the Project Name, a Sum of the total budget in the Rows area and then show the actuals by month with a total actuals spend at the end. I have a slicer that, when a new financial year is selected, the Matrix will update.

SamWhitehouse00_2-1660152308358.png

 

However, when selecting a different year, the actuals update to reflect the year but the Total Budget does not update. 

SamWhitehouse00_3-1660152539894.png

 

What i want is for the formula to look like the below, 

"Total Budget = CALCULATE(sum(Actuals[Value]),Actuals[FY]= Selected date from slicer)". I have tried to do this but get the below error

 

SamWhitehouse00_4-1660152663703.png

 

Any Help

 

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

At the very least, create a Calendar Table with a relationship (Many to One and Single) from the Date column in the Actual and Budget tables to the Date column in the Calendar Table.  You will have to create a Date column in the Actual and Budget tables from the Year and Month columns that are there. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

Please do not specify table names for measures. These are meaningless.

You need to protect your measure value from the CALCULATE context transition. Put the [FY Value] measure into a variable outside the CALCULATE and then reference by the variable name.

 

Why is Budget impacting FY2 ?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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