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

[Jadon]

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:

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

[v-jayw-msft]

Hi @Jadon ,

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

Best Regards,
Jay

[Jadon]

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

Date	Facility	Link Type	Hours
Jan-17	A	WI	56
Jan-17	A	LT	75
Jan-17	A	WI	32
Jan-17	B	WI	56
Jan-17	B	WI	75
Jan-17	B	PO	32
Jan-17	C	WI	56
Jan-17	C	LT	75
Jan-17	C	WI	32
Feb-17	A	WI	56
Feb-17	A	LT	75
Feb-17	A	PO	32
Feb-17	B	WI	114
Feb-17	B	WI	33
Feb-17	B	PO	75
Feb-17	C	WI	12
Feb-17	C	WI	39
Feb-17	C	WI	54
Feb-17	A	WI	56
Feb-17	A	LT	321
Feb-17	A	WI	34

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:

Facility	2017
ID	Jan	Feb	TOTAL
A	56	56	112
B	75	114	189
C	56	54	110

[v-jayw-msft · 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])

Best Regards,

Jay