Add a new row to Matrix based on values from other rows

[KGillett]

Hi,

I've spent more time than I'd like to admit trying to work this out myself, but I've run out of ideas.  I suspect this isn't that difficult, if you know where to look.  Appreciate any guidance.

I have managed to create a series of unions (in DAX) that summarise information and populate nicely into a Matrix.  The visual's data looks like this (with the names hidden by the green blobs):

This is what I'm trying a achieve: 

I'm trying to add the highlighted rows to the Matrix by simply doing a calulation of 'Availability' minus 'Bookings' for each Person.  I'll call this 'Balance' and show it at the bottom of each Person's records.  I have been trying to add another union to do the calculation, but I can't get that to work.

Guidance appreciated!

[KGillett]

Actually, your question @tamerj1 sent me back to basics and I reassessed how data should be served up to work in a Matrix.  I modified the data structure and it is working fine.  I had 3 data tables coming in and was trying to make it too clever.  Sometimes it's too easy to get buried in a complex methodology and miss the basics!

[tamerj1 · Accepted solution]

Hi @KGillett 

how does the source data look like?

[KGillett]

Hi @tamerj1 

I think I've found a way.  I created a key between my summary tables and used a lookupvalue to pull the data across.  I now have the 'Balance' showing correctly in the pivot.  I'll post more info when I've worked it through and maybe you can show me how I should have done it.  Thank you!