Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Matrix subtotal column sum columns that are max aggregated

‎11-15-2022 10:48 AM

I have a matrix that displays the max operating hours for each facility per year and month that come from reports with a WI identification as such:

Jadon_0-1668537183773.png

 

But I would like to get it so that the Total column shows a sum for that year. (This matrix continues on the to right for the next years as well with their own corresponding Total columns). Currently I am making it only consider 'WI' reports by simply filtering the matrix for 'WI', so I am not sure if I would need to do it differently in this scenario.

I have tried a couple of solutions I found on this forum, but haven't gotten it to work yet.

 

Here is a sample containing a dataset for a facility in the year of 2015.

I want to basically have a table identical to the one above, but with the Total row summing the previous max, and each column showing the max Hours for that facility in that year and month where the FromToID is = 'WI'

YearMonthReportingFacilityIDFromToIDHours
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333GS0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333WI0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333 0
2015FebruaryABBT0127333WI0
2015FebruaryABBT0127333WI668
2015FebruaryABBT0127333WI0
2015FebruaryABBT0127333WI0
2015FebruaryABBT0127333WI0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333GS0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333WI0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333 0
2015MarchABBT0127333WI0
2015MarchABBT0127333WI744
2015MarchABBT0127333WI0
2015MarchABBT0127333WI0
2015MarchABBT0127333WI0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333GS0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333WI0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333 0
2015AprilABBT0127333WI0
2015AprilABBT0127333WI691
2015AprilABBT0127333WI0
2015AprilABBT0127333WI0
2015AprilABBT0127333WI0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333GS0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333WI0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333 0
2015MayABBT0127333WI0
2015MayABBT0127333WI680
2015MayABBT0127333WI0
2015MayABBT0127333WI0
2015MayABBT0127333WI0
2015JuneABBT0127333 0
2015JuneABBT0127333 0
2015JuneABBT0127333GS0
2015JuneABBT0127333 0
2015JuneABBT0127333 0
2015JuneABBT0127333WI0
2015JuneABBT0127333 0
2015JuneABBT0127333 0
2015JuneABBT0127333 0
2015JuneABBT0127333 688
2015JuneABBT0127333 0
2015JuneABBT0127333WI0
2015JuneABBT0127333WI658
2015JuneABBT0127333WI0
2015JuneABBT0127333WI0
2015JuneABBT0127333WI0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333GS0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333WI0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333 0
2015JulyABBT0127333WI0
2015JulyABBT0127333WI685
2015JulyABBT0127333WI0
2015JulyABBT0127333WI0
2015JulyABBT0127333WI0
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333GS712
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333WI0
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333 0
2015AugustABBT0127333WI0
2015AugustABBT0127333WI559
2015AugustABBT0127333WI0
2015AugustABBT0127333WI0
2015AugustABBT0127333WI0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333GS0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333WI0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333 0
2015SeptemberABBT0127333WI0
2015SeptemberABBT0127333WI653
2015SeptemberABBT0127333WI0
2015SeptemberABBT0127333WI0
2015SeptemberABBT0127333WI0
2015OctoberABBT0127333 0
2015OctoberABBT0127333 0
2015OctoberABBT0127333GS682
2015OctoberABBT0127333 0
2015OctoberABBT0127333 694
2015OctoberABBT0127333WI0
2015OctoberABBT0127333 0
2015OctoberABBT0127333 0
2015OctoberABBT0127333 0
2015OctoberABBT0127333 0
2015OctoberABBT0127333 0
2015OctoberABBT0127333WI0
2015OctoberABBT0127333WI718
2015OctoberABBT0127333WI0
2015OctoberABBT0127333WI0
2015OctoberABBT0127333WI0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 0
2015NovemberABBT0127333GS0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 700
2015NovemberABBT0127333WI0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 0
2015NovemberABBT0127333 0
2015NovemberABBT0127333WI0
2015NovemberABBT0127333WI709
2015NovemberABBT0127333WI0
2015NovemberABBT0127333WI0
2015NovemberABBT0127333WI0
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333GS682
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333WI0
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333 0
2015DecemberABBT0127333WI0
2015DecemberABBT0127333WI726
2015DecemberABBT0127333WI0
2015DecemberABBT0127333WI0
2015DecemberABBT0127333WI0
Labels:
Message 1 of 3
123 Views
0
2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

‎11-16-2022 01:49 AM

Hi @Anonymous ,

 

It seems to not quite understand your needs, can you provide a screenshot of the desired result so that I can easily answer it for you as soon as possible.


Looking forward to your reply.


Best Regards,
Henry

 

Message 2 of 3
116 Views
0
Anonymous
Not applicable
In response to v-henryk-mstf

‎11-16-2022 08:07 AM

Hi Henry, here is a basic visual of what I would want to have as a result for the sample data I provided:

Year2015           
Reporting Facility IDFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
ABBT01273336687446916806586855596537187097267491

This matrix would have more years continuing to the right using the real data, but since the real data is so large I only have provided a single year.

Basically for each month it takes the maximum value of hours, but it only considers entries with the FromToID being equal to WI

The subtotal columns that would appear after each year should sum those maximums for that year.

Hopefully that clears things up.

Message 3 of 3
104 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All