Creating new category using data from two excel

[tobiasmcbride]

I have some budget and actuals data for various categories (these categories are the same across the two datasets):

Category	Budget	Actuals
Direct Staff Costs	£XXX	£xxx
Indirect Staff Costs	£XXX	£xxx
Bonus	£xxx	£xxx
Travel	£xxx	£xxx
Marketing	£xxx	£xxx

The budget and actuals data are pulled from two different Excel spreadsheets but as the categories are common we can get them to match up.

However when presenting this in the report, I'd like to combine the 3 staff costs (Direct, Indirect & Bonuses) above into a new category called 'Staff Costs' alongside the other costs we have above. This would be across the Budget and Actuals dataset.

How is it best to do this and combine those 3 categories together? 

[v-frfei-msft · Accepted solution]

Hi @tobiasmcbride ,

Please create a new calculated table as below to work on it.

Table = 
ADDCOLUMNS (
    DISTINCT ( UNION ( VALUES ( Actuals[Category] ), VALUES ( Budget[Category] ) ) ),
    "NEW", IF (
        [Category] IN { "Direct Staff Costs", "Indirect Staff Costs", "Bonus" },
        "Staff Costs",
        Actuals[Category]
    )
)

After that, make the relationships between tables like that.

Pbix as attached.

[jherralo95]

Hi @tobiasmcbride!

You can try to create an additional column in the Excel file or create a custom columns in Power BI with the following code:

Column = IF(
            OR('TableName'[Category]="Direct Staff Costs";
               OR(
               'TableName'[Category]="Indirect Staff Costs";
               'TableName'[Category]="Bonus"
               )
            );
             "Staff Costs";
             'TableName'[Category]
           )