Forum Discussion

Arun Chandramouli's avatar
Arun Chandramouli
Brass Contributor
Jan 06, 2022
Hi Sergei, Really appreciate your response...The only problem with this is that the percentages are going beyond 100 when the number of times the code is present in predictedcode is greater than the number of times the code is present in the agentcode...So the logic that I am looking to build is that for a unique code in the agentcode (Number of encounters that have the matching code in predictcode for the agent code)/ total number of encounters with code in the agent code column.. An encounter is a combination of ctext id and visitbillid... For example consider a agentcode C102 for an encounter (ctextid:3421 vbillid : 231) if the same code is present in predictcode for the same encounter(ctextid:3421 vbillid : 231 then the matching count is 1 ..(Numerator -> Number of matching counts for the code/ Denominator -> Number of times the code appears in the agentcode column)..Basically I am trying to calculate the precision for a particular agent code
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 06, 2022

    Arun Chandramouli 

    Could you please generate sample file for testing of such behaviour. In current one I see nothing > 100% with current PQ logic as well as no agentcode C102 and ctextid:3421 you mentioned.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 07, 2022

        Arun Chandramouli 

        Thank you for the sample.

         

        Perhaps like this

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    agentList = Table.AddColumn(
        Source,
        "agentlist",
        each List.Transform(
            Text.Split([agentcode], "," ),
            each Text.Trim(_) )),
    predictList = Table.AddColumn(
        agentList,
        "predictlist",
        each List.Transform(
            Text.Split([predictcode], "," ),
            each Text.Trim(_) )),
    #"Expanded agentlist" = Table.ExpandListColumn(
        predictList,
        "agentlist"),
    matching = Table.AddColumn(#"Expanded agentlist",
        "Total Matching",
        each fnCount( [predictlist], [agentlist] )),
    #"Removed Other Columns" = Table.SelectColumns(
        matching,
        {"agentlist", "Total Matching"}),
    #"Grouped Rows" = Table.Group(
        #"Removed Other Columns",
        {"agentlist"},
        {
            {"total number", each Table.RowCount(_), Int64.Type},
            {"total matching", each List.Max([Total Matching]), type number}}),
    percent = Table.AddColumn(
        #"Grouped Rows",
        "% matching",
        each [total matching] / [total number], Percentage.Type)
in
    percent

        with function

        (lst as list, v as any) =>
let
    Source = List.Count( List.Select( lst, each _ = v) )
in
    Source

Resources