Retrieve Top 10 issues across tables considering variance & errors weightage

Hi, sanal5677 

According to your description and Follow-up, I can roughly understand your requirement. Then I do some operations in Power BI to get a table similar to your expected table, you can take a look at my steps:

1. Finished some merge query and expand table operations in the Power query:

‘Merge1’ is the table I got:

This is the M query in the advanced editor:

let
    Source = Table.NestedJoin(RunData, {"StepID"}, StepDetails, {"StepID"}, "StepDetails", JoinKind.LeftOuter),
    #"Expanded StepDetails" = Table.ExpandTableColumn(Source, "StepDetails", {"StepName"}, {"StepDetails.StepName"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded StepDetails", {"RunID"}, ParentAppRunMapper, {"RunID"}, "ParentAppRunMapper", JoinKind.LeftOuter),
    #"Expanded ParentAppRunMapper" = Table.ExpandTableColumn(#"Merged Queries", "ParentAppRunMapper", {"ParentAppID"}, {"ParentAppRunMapper.ParentAppID"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ParentAppRunMapper", {"ParentAppRunMapper.ParentAppID", "StepID"}, HistoricalRun, {"ParentAppID", "StepID"}, "HistoricalRun", JoinKind.LeftOuter),
    #"Expanded HistoricalRun" = Table.ExpandTableColumn(#"Merged Queries1", "HistoricalRun", {"Duration"}, {"HistoricalRun.Duration"})
in
#"Expanded HistoricalRun"

2. Apply and close the Power query, then create a calculated table like this:

Table =

var _Variance=

SELECTCOLUMNS('Merge1',"RunID",[RunID],"VarianceName",[StepDetails.StepName],"Variance",[Variance],"Issue Type","Variance")

var _Error=

SELECTCOLUMNS('Error',"RunID",[RunID],"VarianceName",[Error],"Variance",[Count],"Issue Type","Error")

return

UNION(_Variance,_Error)

3. Create two calculated columns in the table:

weightage =

IF(

    [Issue Type]="Variance"&&[Variance]>0,BLANK(),

IF([Issue Type]="Error",[Variance]*10,ABS([Variance])))

Issue ID = RANKX('Table',[weightage],,DESC,Dense)

4. Create a table and place fields and apply filter like this:

And I guess this can be what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.