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.

DataInsights's avatar
DataInsights
Super User
5 years ago

Anonymous,

 

Try this measure:

 

Average Derived Value = 
CALCULATE ( AVERAGE ( Metrics[derived value] ), ALLEXCEPT ( Metrics, Metrics[Level 1 Risk] ) )