Building a Specific Matrix Table

[Anonymous]

Hi, I have two tables of data that I need to analyze and show budget vs actuals:

Name	Month	Transaction Amount
Bill Gates	January	65
Bill Gates	January	88
Bill Gates	February	19
Bill Gates	February	43
Bill Gates	February	69
Bill Gates	February	78
Bill Gates	March	64
Bill Gates	March	12
Bill Gates	March	39
Bill Gates	March	51
Tim Cook	January	47
Tim Cook	January	69
Tim Cook	February	21
Tim Cook	February	85
Tim Cook	February	13
Tim Cook	February	27
Tim Cook	March	68
Tim Cook	March	13
Tim Cook	March	29
Tim Cook	March	98

Name	Budget
Bill Gates	600
Tim Cook	500

I have been able to make the matrix look like this:

Name	Transaction Sum	Budget	Budget Remaining
Bill Gates	528	600	72
Tim Cook	470	500	30

However, I am trying to add the months into the drill down to look like this: Notice how the budget remaining includes a rolling total and the budget is divided into the months:

Name	Transaction Sum	Budget	Budget Remaining
Bill Gates
January	153	200	447
February	209	200	238
March	166	200	72
Total	528	600	72

Honestly, I would really prefer the table to look like this below, but I do not think it is possible. I would love for someone to prove me wrong:

Name	January	February	March	Total Transaction Amount	Budget	Budget Remaining
Bill Gates	153	209	166	528	600	72
Tim Cook	137	125	208	470	500	30

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following measures:

Actuals Value = SUM(Actuals[Transaction Amount])

Budget Total = if (HASONEVALUE(Actuals[Month]);BLANK();SUM(Budget[Budget]))

Remaining = IF([Budget Total] = BLANK();BLANK();[Budget Total] - Budget[Actuals Value])

Then add the values on a matrix table:

• Rows: Budget[Name]
• Columns: [Actuals Month]
• Values:
  • Actuals Value
  • Budget Value
  • Remaining

This will give the table below:

Has you can see the Budget Total and reaming columns are blank on the values just resize those columns until they are not visible and be sure to turn off wrap text on  the options:

The months are not in the correct order because is just a mockup. See attach PBIX file.

Regards,

MFelix

[Anonymous]

Thank you very much @MFelix. I was really overthinking it 🙂