Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Issue in creating data table using summarize

hello all,   I have data table created that shows only the latest date metric ID's, now that i need to calculate the average of derived value column by grouping level 1 risk column but i am unable ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Anonymous ,

     

    Try adding two columns to the result table.

    VAR Result =
SELECTCOLUMNS (
CALCULATETABLE ( archer_kri_latest_summary, MaintainDataLineage ),
"metric_date", archer_kri_latest_summary[metric_date],
"metric_id", archer_kri_latest_summary[metric_id],
"derived value", archer_kri_latest_summary[derived_value],
"Level 1 Risk", archer_kri_latest_summary[Level I Risk],
"Segment Type", archer_kri_latest_summary[Segment Type],
"Status", archer_kri_latest_summary[status],
"sum",CALCULATE(SUM(archer_kri_latest_summary[derived_value]),ALLEXCEPT(archer_kri_latest_summary,archer_kri_latest_summary[Level I Risk])),
"average",CALCULATE(AVERAGE(archer_kri_latest_summary[derived_value]),ALLEXCEPT(archer_kri_latest_summary,archer_kri_latest_summary[Level I Risk])),
)

     

     

     

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

V-lianl-msft's avatar
V-lianl-msft
Community Support
5 years ago

Hi Anonymous ,

 

Try adding two columns to the result table.

VAR Result =
SELECTCOLUMNS (
CALCULATETABLE ( archer_kri_latest_summary, MaintainDataLineage ),
"metric_date", archer_kri_latest_summary[metric_date],
"metric_id", archer_kri_latest_summary[metric_id],
"derived value", archer_kri_latest_summary[derived_value],
"Level 1 Risk", archer_kri_latest_summary[Level I Risk],
"Segment Type", archer_kri_latest_summary[Segment Type],
"Status", archer_kri_latest_summary[status],
"sum",CALCULATE(SUM(archer_kri_latest_summary[derived_value]),ALLEXCEPT(archer_kri_latest_summary,archer_kri_latest_summary[Level I Risk])),
"average",CALCULATE(AVERAGE(archer_kri_latest_summary[derived_value]),ALLEXCEPT(archer_kri_latest_summary,archer_kri_latest_summary[Level I Risk])),
)

 

 

 

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