Adding Calculated Column to Matrix - based on Column values

[bvandyke]

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

[v-achippa]

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

[v-achippa]

Hi @bvandyke,

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

[cengizhanarslan · Accepted solution]

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.

[bvandyke]

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?

Thanks

[rajendraongole1]

Spoiler

Hi @bvandyke - you can create a base measure

Total Hour = SUM( Labor[Hours] )

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

[rajendraongole1]

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.