Calculate average of a measure or create new column

[sccoleman1189]

Hello,

I have some times that I am trying to get the average of:

Overview:

There are individuals that have the same activity and I am trying to find the average time (occurred) of each activity. Below is an example of an activity that I want to calculate the average first onsite for  :

I created the measure to select the min of this activity:

CALCULATE(MIN('Tech Activity'[OCCURRED]),'Tech Activity'[CORE_DESCRIPTION]="AsnAssignmentOnSite_evt")

After creating this measure, I want to take the average but im having difficulty because I can only take the average of a column.

Can someone give me advice on how to calculate the average?

Thanks!

[Anonymous]

Hi @sccoleman1189 

Create 2 measures as below,

1. Min Occurred =MIN('Tech Activity'[OCCURRED])

2. Average Occurred = AVERAGEX(FILTER('Tech Activity','Tech Activity'[CORE_DESCRIPTION]="AsnAssignmentOnSite_evt"), Min Occurred)

Please let me know if this helps.

[v-chuncz-msft]

@sccoleman1189 

You may try using AVERAGEX.

• https://community.powerbi.com/t5/Desktop/Average-of-a-measure-for-each-value-of-the-years/m-p/312310...
• https://community.powerbi.com/t5/Desktop/Change-table-visualization-total/m-p/323290#M144103

[Anonymous]

Hi @sccoleman1189 ,

I am hoping that you have already tried basic dax calculations.

I think there are many ways to do it but I prefer two ways

1. If data is coming from db then you can make necessary calculations in DB and make separate views as per requirement then take average in power bi.

2. Using Dax make a summary table with your data then take the average of that column.

 I am hoping this will work, If not you can revert with the sample pbix file 

Best Regards,

Ravi

[sccoleman1189]

I tried to select the min and max in the database but it returned the same value for each column. Not sure how to make a summary table. I new to Power BI

[Anonymous]

Did you try Dax which @santiagomur  mentioned above? 

[sccoleman1189]

Yes. Because I need to capture the min and max of each activity then average each, the DAX that was provided doesnt work.

[Anonymous]

Give me sample data or pbix i will work on that. 

[santiagomur]

avg activity=
var maxcal=CALCULATE(MAX('Tech Activity'[OCCURRED]),FILTER('Tech Activity',[CORE_DESCRIPTION]="AsnAssignmentOnSite_evt"))

var mincal=CALCULATE(MIN('Tech Activity'[OCCURRED]),FILTER('Tech Activity',[CORE_DESCRIPTION]="AsnAssignmentOnSite_evt"))

return
(maxcal+mincal)/2

[santiagomur]

CALCULATE(AVERAGE('Tech Activity'[OCCURRED]),FILTER('Tech Activity',[CORE_DESCRIPTION]="AsnAssignmentOnSite_evt"))