Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)
And what I would need is for the total column and row to instead be summing the values in the matrix.
Solved! Go to 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])
Best Regards,
Jay
Hi @Anonymous ,
Please share some sample data and expected result so that we could test the formula.
Best Regards,
Jay
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 |
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])
Best Regards,
Jay
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |