Calculated column help

[vjnvinod]

Hi All,

Below is my few calculated coloumns of a Table "Data" in the screenshot

I would like to create another calculated coloumn "Performance" based on below parameter

how to do this?

		Performance
TER MLO	>95%	Top
ANSR/TER Ratio	>85%	Top
Margin %	>50%	Top
NUI	<2.4	Top
AR	<2.1	Top
TI	<4.5	Top
TER MLO	85% - 95%	Average
ANSR/TER Ratio	80% - 85%	Average
Margin %	45% - 50%	Average
NUI	2.4 - 2.7	Average
AR	2.1 - 2.3	Average
TI	4.5 - 5.0	Average
TER MLO	75% - 85%	Below Average
ANSR/TER Ratio	75% - 80%	Below Average
Margin %	40% - 45%	Below Average
NUI	2.7 - 3.0	Below Average
AR	2.3 - 2.5	Below Average
TI	5.0 - 5.5	Below Average
TER MLO	<75%	Low
ANSR/TER Ratio	<75%	Low
Margin %	<40%	Low
NUI	>3.0	Low
AR	>2.5	Low
TI	>5.5	Low

[v-eqin-msft · Accepted solution]

Hi @vjnvinod ,

According to my understand , you want to display Performance from another table for each Type in specific range, right?

You need to transform Performance Table firstly:

1. Add a LargeThan column and replace all blank values with 0.

2. Add a LessThan column and replace all blank values with 100%.

The whole M syntax is shown below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJdC4IwFIb/yhh0ZzZzQ7ss6CLQgmVX5oXIkCAyoo+/3zzLeVrmnfC8nr0PvHlOs7UkabKjHj0+GAvVQkz0d9ZcaeHldLndy1kbkeX91NhQ/BVKy1t9upCJxYJhvD1sOlLNfY6vSwQCBLL+D+4LDGxbXYFMiWm7fKpbWavhxjFrg/FPELXmcMu0xhHTXHfWdO5H7ksSYAAwdCAY6O7tXZ+50FpEoi+3UufmRUZdPnE2HMdGIM3/3O28Ip0Jod3A28YuBDsxGAFHbQeOfyLf66oiaJQ0r5F1OSF3XRVnGKN1KeNir9t1KWPQgX5dSvSgeAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Val = _t, Performance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Val", type text}, {"Performance", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "LargeThan", each if Text.StartsWith([Val],">") then Text.AfterDelimiter([Val],">") else  if Text.StartsWith([Val],"<") then Text.BeforeDelimiter([Val],"<") else Text.BeforeDelimiter([Val],"-")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","","0",Replacer.ReplaceValue,{"LargeThan"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "LessThan", each if Text.StartsWith([Val],">") then Text.BeforeDelimiter([Val],">") else  if Text.StartsWith([Val],"<") then Text.AfterDelimiter([Val],"<") else Text.AfterDelimiter([Val],"-")),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Custom1","","100%",Replacer.ReplaceValue,{"LessThan"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"LargeThan", type number}, {"LessThan", type number}})
in
    #"Changed Type1"

Then you could add some calculated columns using the following DAX formula: (Take ANSR/TER and NUI as examples)

ANSR/TER Performance =
VAR _ansr = [ANSR/TER]
RETURN
    CALCULATE (
        MAX ( 'Performance Table'[Performance] ),
        FILTER (
            'Performance Table',
            'Performance Table'[Type] = "ANSR/TER Ratio"
                && 'Performance Table'[LessThan] >= _ansr
                && 'Performance Table'[LargeThan] <= _ansr
        )
    )

NUI Performance =
VAR _nui =
    VALUE ( RIGHT ( [NUI], 3 ) )
RETURN
    CALCULATE (
        MAX ( 'Performance Table'[Performance] ),
        FILTER (
            'Performance Table',
            'Performance Table'[Type] = "NUI"
                && 'Performance Table'[LessThan] >= _nui
                && 'Performance Table'[LargeThan] <= _nui
        )
    )

 My final Data table looks like this:

Please take a look at my pbix file here:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/stephent01_qiuyunus_onmicrosoft_com/EfKAVPFPs41Ntm...

Did I answer your question ? Please kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem.

Best Regards,
Eyelyn Qin