cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dsharma43
Helper II
Helper II

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 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 RiskSegment Typemetric_datemetric_idderived valueStatus
TechnologyGlobal10/31/2020 0:00179942796Active
TechnologyGlobal10/31/2020 0:00179942698Active
Change ManagementGlobal9/30/2019 0:00179947066Inactive
Change ManagementCanadian9/30/2019 0:00179947450Inactive
Change ManagementGlobal9/30/2019 0:001799471100Active
Change ManagementCanadian3/31/2019 0:001799475100Active

 

Thanks 

D

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @Dsharma43 ,

 

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.

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

Hi @Dsharma43 ,

 

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.

View solution in original post

DataInsights
Super User II
Super User II

@Dsharma43,

 

Try this measure:

 

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

 

DataInsights_0-1612120218803.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.