cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.