Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Building a Specific Matrix Table

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
1 ACCEPTED SOLUTION
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Anonymous
Not applicable

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors