New Enterprise DNA Initiatives

Matrix YTD & Variance

Hi, I have a P&L matrix with account codes for revenue and costs on the rows, I have my months on my columns with my measure for the values and this works well. But i now want to show YTD Actuals, YTD budget & YTD Variance columns at the end of my matrix but when i put my measure into my matrix it puts the values under each individual month rather than displaying single columns at the end of my matrix. I’ve searched on google and on this forum but haven’t found a solutions yet.

I then thought about creating a new matrix to the side just with the YTD, YTD BUD and YTD Variance columns in this matrix, this works until i need to scroll down as the P&L has many accounts, as one matrix scrolls down but the other stays the same so this doesn’t fully work, can you sync scroll bars for two matrix?

Any help with best option would be appreciated.

It would be grt you shared a pbix file and your desired output .So i can try my hand on it.

Thanks,
Anurag

Hi @Anurag I can’t upload the file as it has confidential informatiion on it but to visualise what i am trying to do see the picture below. I would normally put the totals and variances in another matrix beside the main month figures and line it up so nobody can tell it’s a seperate visual, the problem i have with my live data is that there are so many rows when i scroll down one visual the other visual doesn’t move. So i am back to trying to put all the columns in the same matrix visual but when i create the measures and put onto my visual PBI puts the budet values and the variances under each monthly column rather than at the end.

Hi @BCS. If possible, please sanitize your data or create sample data and attach your work-in-progress PBIX file along with an Excel mock-up of exactly the outcome you’re looking for. The forum is composed of volunteers who freely give their time and are committed to the learning of others, so any help you can give them would be appreciated. Please see the article at the top of the thread list on asking questions on the eDNA support forum. You can also use the techniques described in @BrianJ’s YouTube video to mask your data.
Greg

https://forum.enterprisedna.co/t/asking-questions-on-the-enterprise-dna-support-forum

Hi @Anurag, @Greg see attached extract of my data, excuse some of the figures as things went a bit wonky when I was extractinng the data but the form remains the same.

Matrix Display Issue.pbix (422.6 KB) So basically I am trying to include the YTD BUD. and YTD Diff columns within the main monthly matrix. But when i do this the matrix puts the budget values under each month column. If the chart of accounts wasn’t so big i would just keep the seperate matrix tables side by side but when you use the slicer to go down the levels and scroll one matrix table the other one obviously does not scroll in sync. Any help and or suggestions would be appreciated.

Hello @BCS,

Thank You for posting your query onto the Forum as well as for attaching the reference PBIX file.

Is this the type of result that you’re looking for? Below is the screenshot provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Matrix Display Issue - Harsh.pbix (419.9 KB)

Hi @Harsh

Thanks for your reply, that works great but is there any way to automate hiding the columns?

When you add a new month e.g. November the end user would have to manually hide the columns for the new month which isn’t ideal from an end user perspective.

Thanks for your help

Hello @BCS,

I’m sorry to say this but this is the only way out. As of now, there’s no option available in Power BI to automatically hide the columns.

Thanks and Warm Regards,
Harsh

Hi @Harsh

That’s too bad, that would have worked a treat otherwise.

I keep on thinking there must be a way to bypass the month columns and just have the total at the end via the measure itself but i don’t know what that solution would be.

Thanks for your help

Hi @BCS, we’ve noticed that no response has been received from you since the 9th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi @BCS, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!