cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jadon
Frequent Visitor

Matrix with max aggregation where the total is a sum of maxes.

I have a matrix that shows the hours a facility was active based on a maximum of hours from linked locations. I can show this, but then the total column for each year and overall is still a max and I want to prevent that from happening.

The values I am pulling the max from are filtered based on a type of linked location, as only some are relevant.

I have created the following measure:

 

 

 

Max Hours =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            VolumetricData,
            VolumetricData[Hours]
        ),
        "MaxWell",
            CALCULATE(
                IF(MAX('VolumetricData'[FromToIDType])=="WI", MAX(VolumetricData[Hours]), 0)
            )
    ),
    [MaxWell]
)

 

The MAX on the FromToIDType is only because as far as I understand the measure has to aggregate values to use them, and I'm not sure how to make that part more correct.
trying to make this work based on a post I saw on this forum, but it doesn't do what I need it to do.
Here is the current:
Jadon_0-1667504825651.png

And what I would need is for the total column and row to instead be summing the values in the matrix.

 

1 ACCEPTED SOLUTION

Hi @Jadon ,

 

Please refer this formula:

Measure = 
var tmp = SUMMARIZE(FILTER('Table','Table'[Link Type]="WI"),'Table'[Date],'Table'[Facility],"hours",MAX('Table'[Hours]))
return
SUMX(tmp,[hours])

vjaywmsft_0-1669626119086.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Jadon ,

 

Please share some sample data and expected result so that we could test the formula.

 

Best Regards,
Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

I can't provide the actual sample data due to confidentiality issues, so the best I can do is make a very small sample set to show how it should work

DateFacilityLink TypeHours
Jan-17AWI56
Jan-17ALT75
Jan-17AWI32
Jan-17BWI56
Jan-17BWI75
Jan-17BPO32
Jan-17CWI56
Jan-17CLT75
Jan-17CWI32
Feb-17AWI56
Feb-17ALT75
Feb-17APO32
Feb-17BWI114
Feb-17BWI33
Feb-17BPO75
Feb-17CWI12
Feb-17CWI39
Feb-17CWI54
Feb-17AWI56
Feb-17ALT321
Feb-17AWI34

Given an input like this I want to be able to make a matrix with the following design:
Rows: Facility ID

Columns: Year, Month

Values: Max of Hours where Link Type = WI

But where the total column actually sums, as currently it still just takes the Max

The goal would be a matrix that looks something like:

Facility2017  
IDJanFebTOTAL
A5656112
B75114189
C5654110

Hi @Jadon ,

 

Please refer this formula:

Measure = 
var tmp = SUMMARIZE(FILTER('Table','Table'[Link Type]="WI"),'Table'[Date],'Table'[Facility],"hours",MAX('Table'[Hours]))
return
SUMX(tmp,[hours])

vjaywmsft_0-1669626119086.png

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.