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