topic Re: Add a new row to Matrix based on values from other rows in DAX Commands and Tips

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

KGillett — Wed, 21 Jun 2023 09:30:58 GMT

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!

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

tamerj1 — Wed, 21 Jun 2023 09:52:53 GMT

Hi KGillett

how does the source data look like?

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

KGillett — Wed, 21 Jun 2023 11:03:13 GMT

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!

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

KGillett — Thu, 22 Jun 2023 11:57:41 GMT

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!