Issue in creating data table using summarize

[Anonymous]

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 to succeed in that. could you please help me with this.

Please see the dax below used to obtian the metric ID that are of latest date.

, ** is where is am trying to put summarize dax to group level 1 risk and get the derived value from the output of result table but the result is not showing up correct

Table 2 =
VAR MetricIdAndRecentDate =
SUMMARIZECOLUMNS ( archer_kri_latest_summary[metric_id], "RecentDate", MAX ( archer_kri_latest_summary[metric_date] ) )
VAR MaintainDataLineage =
-- Treats RecentDate virtual column as metric date which helps in filtering original data
TREATAS ( MetricIdAndRecentDate, archer_kri_latest_summary[metric_id], archer_kri_latest_summary[metric_date] )
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]
)
****trying to summarise by level 1 risk column and get average of derived value**

return Result

result from the above dax, now i need to calculate average of derived value from this result table by grouping level1risk column

Level 1 Risk	Segment Type	metric_date	metric_id	derived value	Status
Technology	Global	10/31/2020 0:00	1799427	96	Active
Technology	Global	10/31/2020 0:00	1799426	98	Active
Change Management	Global	9/30/2019 0:00	1799470	66	Inactive
Change Management	Canadian	9/30/2019 0:00	1799474	50	Inactive
Change Management	Global	9/30/2019 0:00	1799471	100	Active
Change Management	Canadian	3/31/2019 0:00	1799475	100	Active

Thanks 

D

[V-lianl-msft · Accepted solution]

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]

@Anonymous,

Try this measure:

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