cancel
Showing results for
Did you mean:
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(
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.

1 ACCEPTED SOLUTION
Community Support

``````Measure =
return
SUMX(tmp,[hours])``````

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.
3 REPLIES 3
Community Support

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.
Frequent Visitor

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
Community Support

``````Measure =
return
SUMX(tmp,[hours])``````

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.

Announcements

#### 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.

#### 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.

#### 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!

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors