Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous ,

 

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 @Anonymous ,

 

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.
Anonymous
Not applicable

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 @Anonymous ,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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