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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Daveed
New Member

Display Budget vs Actuals (which are at a lower level)

Hi all,

 

I have a requirement to display budgets vs actuals in a somewhat unique way.

Budgets are set at the Program Level

Actuals are set at the Project Level

 

The client would prefer the matrix/table be able to display the information as:

ProgramProjectBudgetRevenue$ Remaining
MyProgram  200,000 135,000
 Project A 40,000 
 Project B 10,000 
 Project C 15,000 
MyOtherProgram 50,000 49,000
 Project D 1,000 

 

So far I am working on the Budget and Revenue (actuals) measures, with some success.

Budget: IF(HASONEVALUE('Projects'[Project Name]), BLANK(), CALCULATE(SUM('Project Budget'[Budget Amt])))

Revenue:  IF(HASONEVALUE('Program'[Program Name]), BLANK(), CALCULATE(SUM('Project Actuals'[Revenue]))

 

Any suggestions are welcome.

 

thanks

Dave

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

You could try the ISINSCOPE function to help here.

Also, for your matrix formatting, set Stepped Layout to 'Off'. https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual#stepped-layout-with-matrix-v...

Is your Revenue measure working as you would like? What problems are you running into? What is the relationship between tables? Looks like you have at least 4 tables for this

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

2 REPLIES 2
AllisonKennedy
Super User
Super User

You could try the ISINSCOPE function to help here.

Also, for your matrix formatting, set Stepped Layout to 'Off'. https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual#stepped-layout-with-matrix-v...

Is your Revenue measure working as you would like? What problems are you running into? What is the relationship between tables? Looks like you have at least 4 tables for this

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Awesome suggestion - thanks Allison.

 

That DAX function is exactly what i was looking for.

 

Dave

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors