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!

Hello Harsh, when you publish the report, does end users able to see hiding columns please let me know.

Thank you,

Sujit

Hi there,
you might want to start a new posting as this thread has a solution to it. This way it doesn’t get lost and your question doesn’t get answered.
thanks

okay, I will thanks Keith.

Hi @Harsh just wondered if you have seen any recent PBI updates which better suit my original problem? This is a common finance related issue where you want to see months and then variances at the end of the table. If i don’t have any grouping and it’s just a couple of lines of a table i usually create another seperate table at the end and just line then up so they look like they are the same table but if you have too many lines and you need to scroll this obviously doesn;t work as the alignment is then all wrong for the table that is not activated.

Thanks

Hi @BCS,

Please create a new thread since this thread has already been solved 3 months back.

Thanks and Warm Regards,
Harsh

Hi @Harsh the issue wasn’t actually solved hence my question. Surely it’s not efficient in terms of time but also duplication of content creating a brand new thread rather than posting on the original unsolved thread which has all the context of the issue.

Hi @BCS. This thread was marked as solved back in Dec 2020. If you have further questions, a new thread should be created as forum members often only see and pursue unsolved threads.
Greg

Hello @BCS, we understand that your issue 3 months ago was not entirely “Solved”. However, due to the length that the thread was left opened, we opted to close it.

In order to avoid confusion among experts and users, we request that you create a new thread regarding your concern. That way, it’ll be easier for the new contributors to read your concerns.

More details can be found here - Asking Questions On The Enterprise DNA Support Forum.