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

[Daveed]

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:

Program	Project	Budget	Revenue	$ 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

[AllisonKennedy · Accepted solution]

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

[Daveed]

Awesome suggestion - thanks Allison.

That DAX function is exactly what i was looking for.

Dave