calculating average of measure in a seperate column

[saranee]

Hi,

We have been trying to get add a new field in the table visualization which will calculate aggregated value(specifically average) of measure compliance% for all these rows and for all these date range.

formula for comp and compliance%

Comp= DIVIDE([Readings Towards Compliance],[Monthly Frequency Target])

Compliance % = IF(SUM(View_Compliance[Comp %])>1,1,SUM(View_Compliance[Comp %]))+0

Tried using summarize function to create a new table with all my conditions across which i want to calculate average :

SUMMARIZE(View_Compliance,View_Compliance[Asset_Id],View_Compliance[AssetName],View_Compliance[MonitoringName],View_Compliance[MonitoringType],View_Compliance[Region],View_Compliance[Team Leader],View_Compliance[Reservoir Engineer],'Date'[Week Ending],"Comp",Divide(View_Compliance[Readings Towards Compliance],View_Compliance[Weekly Frequency Target])+0)

and then add columns in the same table to calculate average but then can not establish join with other tables to get proper result.

Please can you suggest any solution for this.

[v-yuezhe-msft]

@saranee,

What is your expected result? Could you please share raw data of your original table and post desired result here?

Regards,
Lydia Zhang

[saranee]

Hi,
I am reattaching the pic posted above:
In this pic for the highlighted row value of 3rd column for each week ending is calculated by dividing first column by second column and then by checking if this quotient is greater than 1 then it will be 1 otherwise quotient itself.

formula for comp and compliance%(measure)
Comp= DIVIDE([Readings Towards Compliance],[Monthly Frequency Target])
Compliance % = IF(SUM(View_Compliance[Comp %])>1,1,SUM(View_Compliance[Comp %]))+0

Formula:

Like for the week ending

11/1/2009:value of first column is 0 and that of second column is 1 then value of 3rd column i.e. quotient is 0 or 0%

11/8/2009:value of first column is 1 and that of second column is 1 then value of 3rd column i.e. quotient is 1 or 100%

11/15/2009:value of first column is 1 and that of second column is 1 then value of 3rd column i.e. quotient is 1 or 100%

Average of these 3 quotient is now needed to be shown in a seperate column i.e. 0+100+100/3=66.6%

Thanks in advance.

[v-yuezhe-msft]

@saranee,

Are Comp and Comp % different objects or same measure? How you store the raw data in your original table before you create the Matrix?

Regards,
Lydia