Budget sums across all entries when using a calculated column

[Morasm]

Hi.

I have a problem which I am even having a bit of a hard time explaining.

I have provided the following minimal example with some dummy data.

We have some old sales data in a sales table. It used to be that each sale had a “LegacyCostCenterName” attached to it, but as that proved to inflexible, we have instead added a new column called “OperationsId”. Then we have a table mapping operations IDs to a CostCenterID which references a CostCenter details table. (This makes more sense in my data where it is not sales we work with, but that context is a bit complex explaining here).

Each CostCenterID also has a budget in a budget table.

Since not all operations IDs in the sales table have yet been mapped to a CostCenterID, I have introduced a new column called EffectiveCostCenter. This will use the name from the CostCenterDetails if it exists, otherwise it will use the LegacyCostCentername of the sale row.

These are my tables and relations

This is where the problem comes in. If I create a matrix with the EffectiveCostCenter, it is not able to correctly map the Budget, so for each CostCenter it shows the combined budget of all the cost centers.

Notice that all the budgets are the same, even though they should be different for each Cost Center.

If I use the Cost Center name from the CostCenterDetails table, then the budgets are correct, but then I do not get the correct name for the sales which are not yet mapped to a CostCenter and are using the legacy name.
 

I suspect that the problem is in the FACT_Sales table where the calculated column "EffectiveCostCenter" means we are traversing a number of many-to-one and one-to-many relations which cannot be properly resolved.

Date	LegacyCostCenterName	OpertaionsId	Amount	EffectiveCostCenter
1/1/2024	Old CC 1	A	20	Cost Center A
1/1/2024	Old CC 1	A	20	Cost Center A
1/1/2024	Old CC 1	B	30	Cost Center A
1/1/2024	Old CC 1	C	40	Cost Center A
1/1/2024	Old CC X	X	5	Old CC X
1/1/2024	Old CC 3	D	100	Cost Center B
1/2/2024	Old CC 1	A	10	Cost Center A
1/2/2024	Old CC 1	A	20	Cost Center A
1/2/2024	Old CC 1	B	30	Cost Center A
1/2/2024	Old CC 1	C	70	Cost Center A
1/2/2024	Old CC X	X	5	Old CC X
1/2/2024	Old CC 3	D	150	Cost Center B

You can see the whole example here: minimal example.pbix

I can change most of the tables and relations, except the Sales table which I can only add new columns to

[Anonymous]

Hi @Morasm 

This issue is caused by the relationship between the sales table and budget table:

Maybe you can try the crossjoin() function:

Create a new table with dax:

Table =
FILTER (
    CROSSJOIN (
        FILTER (
            GENERATE (
                SELECTCOLUMNS (
                    'Budget',
                    'Budget'[Budget],
                    'Budget'[CostCenterID],
                    'Budget'[Date]
                ),
                SELECTCOLUMNS (
                    'DIM_CostCenterDetails',
                    'DIM_CostCenterDetails'[CostCenterID],
                    'DIM_CostCenterDetails'[CostCenterName]
                )
            ),
            'Budget'[CostCenterID] = 'DIM_CostCenterDetails'[CostCenterID]
        ),
        'FACT_Sales'
    ),
    'DIM_CostCenterDetails'[CostCenterName] = 'FACT_Sales'[EffectiveCostCenter]
        && 'Budget'[Date] = 'FACT_Sales'[Date]
)

Then create a relationship between this table and the DIM_dates table:

The result is as follow:

Below is the pbix:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Morasm]

Thank you for the reply Zhengdong Xu

The result you showed have summed the budgets on the days. Cost Center A (id: 1) should have a budget of 100 on January 1 and January 2.

But because there was 4 sales on each of thoes two days for Cost Center A (cost center id: 1), the matrix shows a budget of 400.

[Morasm]

I can fix the summing of budgets by using the Average instead. It feels a bit yucky and I wonder if it becomes a problem down the line. For instance if I also want a graphic visualization.

However I also just saw that the matrix is missing the "Old CC X" column for the sale where there is no mapping for the Operations ID

[Morasm]

The suggestion lead me to creating the following table which seems to work on the small dataset. I'll try to implement it in my real report and see what happens:

Table = 
ADDCOLUMNS(
        SUMMARIZE(
            NATURALLEFTOUTERJOIN(
                FACT_Sales, 
                DIM_CostCenterDetails
            ),
            DIM_CostCenterDetails[CostCenterID], 
            DIM_Dates[Dates], 
            FACT_Sales[EffectiveCostCenter], 
            "Amount", SUM(FACT_Sales[Amount]
            )
        ),
        "Budget", CALCULATE(SUM(FACT_Budget[Budget])   
    )
   
)