Matrix Table - only total certain columns

[ecarroll]

I am looking to use a matrix table to total hours worked by week for the current month, but then also have a column for the current YTD hours for that employee, as well as the prior YTD hours for that employee. I have measures for the hours, CYTD hours and PYTD hours, but if I put all 3 measures into the matrix, it adds in the columns for the CYTD and PYTD for each week, but I only need those to show as totals. I've tried various methods (see links below) and can't get them to work. Every time I use SelectedValue, Max, or FirstNonBlank, it does not work. It seems my only option is to hide the columns, which is really not an option at all because this table needs to be automatically updated, not wait for someone to fix it cosmetically each week. Any help would be very much appreciated - if basic Power BI does not work, maybe someone knows of another matrix visual out there that could work? 

Thank you!!

https://community.fabric.microsoft.com/t5/Desktop/Matrix-visual-Measure-to-be-shown-only-in-total-s-...

https://www.youtube.com/watch?v=DIc-KFPr4LI

https://forum.enterprisedna.co/t/dynamic-visuals-displaying-multiple-measure-columns/4355/10

[Anonymous · Accepted solution]

Hi @ecarroll 

I created a simple sample data to do the test for your reference. In sample data, the data is from 1/1/2024 to today and there is only one employee with ID 1.

My sample:

I created the following measure to calculate CYTD.

CYTD = CALCULATE(SUM('Table'[workhours]), FILTER(ALL('Table'), [Date] >= DATE(2024, 1, 1) && [Date] <= TODAY()))

Output:

If this sample data is structurally different from the one you are using, please provide some sample data and the results based on sample data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community  Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.