Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |