Forum Discussion
Adding Calculated Column to Matrix - based on Column values
I'm pretty sure this is possible just not sure which way to approach it. I need to add an Untiliztion value to a matrix. The value is based on the values in the matrix columns (see sample below). I figure I need to create calculated field for this but really don't know how to make that dependant on the column values.
Trying to add teh sbovhUltil column which is = (sbu/ovh column ) / sum (all three columns)
Appreciate the help!
| Name | ovh/ovh | sbu/ovh | sbu/project | sbuovdUltil | |
| John Smith | 155 | 32 | 83% | =155/sum of all three rows | |
| Jill Smith | 28.5 | 154 | 84% | =154/sum of all three row |
Here you go:
OVH_OVH = SUM ( Data[ovh/ovh] ) SBU_OVH = SUM ( Data[sbu/ovh] ) SBU_PROJECT = SUM ( Data[sbu/project] ) SBU OVD Util = VAR Numerator = [SBU_OVH] VAR Denominator = [OVH_OVH] + [SBU_OVH] + [SBU_PROJECT] RETURN DIVIDE ( Numerator, Denominator )Matrix visual:
-
Rows → Name
-
Values →
-
OVH_OVH
-
SBU_OVH
-
SBU_PROJECT
-
SBU OVD Util (the measure above)
-
Format SBU OVD Util as Percentage.
-
6 Replies
Hi bvandyke - If the value depends on what’s shown in the matrix, it must be a measure.
Never a calculated column.Measure:
sbuovhdUtil =
VAR SBU_OVH =
[sbu/ovh]VAR Total =
COALESCE([ovh/ovh], 0)
+ COALESCE([sbu/ovh], 0)
+ COALESCE([sbu/project], 0)RETURN
DIVIDE(SBU_OVH, Total)Hope this helps.
Here you go:
OVH_OVH = SUM ( Data[ovh/ovh] ) SBU_OVH = SUM ( Data[sbu/ovh] ) SBU_PROJECT = SUM ( Data[sbu/project] ) SBU OVD Util = VAR Numerator = [SBU_OVH] VAR Denominator = [OVH_OVH] + [SBU_OVH] + [SBU_PROJECT] RETURN DIVIDE ( Numerator, Denominator )Matrix visual:
-
Rows → Name
-
Values →
-
OVH_OVH
-
SBU_OVH
-
SBU_PROJECT
-
SBU OVD Util (the measure above)
-
Format SBU OVD Util as Percentage.
-
Ok, I put this in and think I understand it. But, getting error "sbu/ovh" cannot be determined. "sbu/ovh" is one of the values in a field (LaborCat) that is set as the Column for the matrix (LaborCat is the column for the matrix).
How do I tell the measure to reference that?
ThanksNow create another mesure for utilización
sbuovhdUtil =
VAR SBU_OVH =
CALCULATE (
[Total Hours],
Labor[LaborCat] = "sbu/ovh"
)VAR Total_All =
CALCULATE (
[Total Hours],
Labor[LaborCat] IN { "ovh/ovh", "sbu/ovh", "sbu/project" }
)RETURN
DIVIDE ( SBU_OVH, Total_All )This should be works.
Check and confirm.
Hi bvandyke,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 and cengizhanarslan for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
