How to get a value from one column based on an expression in another column.

[premm]

I have a table with three columns as mentioned below, I have created a Measure to get the percentage Tot Loged Effort and Expected Effort.

I want to create a measure to get the ”Base Service Unit” name based on maximum percentage in “Percentage Logged/Expected"  (this is a Measure)“

desire output should be Cloud as it has maximum percentage.

I would like to create a measure.

Base Service Unit	Tot Logged Effort	Expected Effort	Percentage Logged/Expected (Measures)
Cloud	16036.85	17468	92%
Data	5708.33	6336	90%
Digital	17221.17	19170	90%
Digital-2	5579	6372	88%

regards,

premm bissht

[premm]

thanks JiHwan Kim,

its working fine for me.

[Remi_Bouvier]

Hi,

Can you try the below measure please?

Base Service Unit max = 
VAR PercBSU = 
ADDCOLUMNS(
    ALLSELECTED('Table'[Base Service Unit]),
    "@Percentage",[Percentage Logged/Expected]
)
Var MaxPerc =
MAXX(
    ALLSELECTED('Table'[Base Service Unit]),
    [Percentage Logged/Expected]
)
VAR Result = 
CALCULATE(
    MAX('Table'[Base Service Unit]),
    FILTER(PercBSU, [@Percentage] = MaxPerc)
)
RETURN 
    Result

I am able to return the correct output.

You can change the ALLSELECTED to VALUES in PercBSU variable to get "Cloud" only showing up on the cloud row.

[Jihwan_Kim · Accepted solution]

Percentage =

AVERAGEX( Data, Data[Tot Logged Effort]/Data[Expected Effort])

MAX percentage Base Service Unit =
VAR maxpercentage =
SUMX (
KEEPFILTERS ( TOPN ( 1, ALL ( Data[Base Service Unit] ), [Percentage], DESC ) ),
[Percentage]
)
RETURN
CALCULATE (
SELECTEDVALUE ( Data[Base Service Unit] ),
FILTER ( ALL ( Data[Base Service Unit] ), [Percentage] = maxpercentage )
)

https://www.dropbox.com/s/slxsr4ezftnx3ru/premm.pbix?dl=0