Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
Solved! Go to 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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
76 | |
73 | |
42 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |