Matrix for budget/actuals with different levels of ganularity

[dbk58]

[Edited for clarity]

Hi all. Has anyone run into a case where actuals are recorded at one level and estimates at another?

I'm building a report for estimates to actuals, where the Estimates are at a project level, but the actuals are recorded at the activity level within project.

So, for example, the Estimate data looks like this:

Job	orderNumber	EstimatedCost
Job0001	Order0001	600
Job0001	Order0002	400
Job0002	Order0003	200

While the Actuals looks like this:

Job	orderNumber	ActivityCode	ActualCost
Job0001	Order0001	code-0001	500
Job0001	Order0001	code-0001	200
Job0001	Order0001	code-0002	100
Job0001	Order0002	code-0001	150
Job0001	Order0002	code-0001	100
Job0002	Order0003	code-0002	100
Job0002	Order0003	code-0002	50
Job0002	Order0003	code-0002	75

What I want the resulting matirx to look like is this:

	Estimate	Actual
Job0001	1000	1050
Order0001	600	800
code-0001	-	700
code-0002	-	100
Order0002	400	250
code-0001	-	250
Job0002	200	225
Order0003	200	225
code-0002	-	225

Not this:

	Estimate	Actual
Job0001	1000	1050
Order0001	600	800
code-0001	600	700
code-0002	600	100
Order0002	400	250
code-0001	400	250
Job0002	200	225
Order0003	200	225
code-0002	200	225

How can rollups be handled where the data does not actually exist at the lowest level?

Thanks.

[v-piga-msft · Accepted solution]

Hi @dbk58 ,

For your requirement, you cannot create the relationship for the two tables.

And then you could create the measure below.

Measure =
IF (
    ISINSCOPE ( Actual[ActivityCode] ),
    BLANK (),
    IF (
        ISINSCOPE ( Actual[orderNumber] ),
        CALCULATE (
            SUM ( Estimate[EstimatedCost] ),
            FILTER (
                Estimate,
                Estimate[Job] = SELECTEDVALUE ( Actual[Job] )
                    && Estimate[orderNumber] = SELECTEDVALUE ( Actual[orderNumber] )
            )
        ),
        CALCULATE (
            SUM ( Estimate[EstimatedCost] ),
            FILTER ( Estimate, Estimate[Job] = SELECTEDVALUE ( Actual[Job] ) )
        )
    )
)

Here is the output.

You also could refer to my attachment.

Best Regards,

Cherry