Average formula

[damit23183]

Hi,

Need help with AVERAGE formula,

I would like to calculate below condition,

ID                   ServiceName        %Value     --> Column Name

1                             A                             0.80

2                             A                             0.88

3                             A                             0.82

4                             B                             0.92

5                             B                             0.96

6                             C                             0.95

7                             C                             0.95

8                             D                             1

9                             E                             0.87

Now when apply filter/slicer on dashboard and select any of service then result should look like below;

                  ServiceName        %Value     --> Column Name                           

                      A                             0.83                                                                                                                                                                             B                             0.94

                      C                             0.95

                      D                             1

                      E                              0.87

This value should change based on selection of Service i.e if choose A then i should only see A servicename and %value which is .83 and so on.

ANy help would be appreciated!

Thanks

[damit23183]

Hi,

Thanks for quick response.

So far data are correct just drilldown option is not working on page.

Result are not expected when i select any service it keep getting same number but SCORE should change if i choose any particular service. For example; please see expected result below;

Service Name	%Value
AAMI	0.87
CUG	0.85
PUG	0.84
AURORA	0.81
Total Average	0.83

As you can see above total average is 0.83 so i am only getting 0.83 whenver i select any service.

But, i would like to see 0.87 when i select AAMI and 0.81 when i select AURORA.

Hope this will enough detail however if you need more please let me know.

Thanks

[v-yiruan-msft]

Hi @damit23183,

Do you mean that when you use service name as slicer filter condition , the total average value displayed is incorrect ? The value of field value% on visual is from the value obtained by the formula AVERAGE (Fac_Incident [Fac_Incident . ResolutionTTR%]? Can you provide some sample data of the three tables you mentioned(Fac_Incident , Customer Experience and another table ) and the created relationships between this 3 table ?

Best Regards

Rena

[damit23183]

Hi,

As you can see below, ConfigItem (Service Table) joined with Fact table on ConfigItem column.

And Fact table and Customer experience (Result Table from Sharepoint) joined on Customer Experience column which is custom column created by me with value 1000 in  both table. The reason is there is no relationship between this two table, that's reason why i have applied SWITCH function to put correct value in front of correct Metric Name.

Both Joins are Many to Many with BOTH cross Filter direction.

Thanks

[v-yiruan-msft]

Hi @damit23183，

Could you please provide your PBIX file if it is convenient?

Best Regards

Rena

[damit23183]

Hi,

Thank you for your response.

However, this has been solved now it was SWICH formula where i did make one mistake but after applied correct syntax it worked perfectly fine.

Thank you all for your support really appreciate it.

Thanks

[v-yiruan-msft · Accepted solution]

Hi @damit23183 ,

Could you please mark this post as Answered since the problem has been resolved by yourself? Thank you.

Best Regards

Rena

[d_gosbell]

---

@damit23183 wrote:

i am only getting 0.83 whenver i select any service.

Oh so if you are seeing the total value repeated for every service and you are using the formula: AVERAGE(Fac_Incident[Fac_Incident.ResolutionTTR%]) that means you do not have a relationship between Fac_Incident and the table with the [Service Name] column (or the relationship is inactive or in the wrong direction). If you fix the relationship I think this should start working.

[damit23183]

Hi,

There is a relationship between Fact table and service table which i have set up in Data model.

However, final table where results are showing does not have direct relationship with any table. Therefore, i did create one custom column in Fact Table and Result table with 1000 value in it, and joined both table with that custom column.

If i do drilldown seperately without result table then its works fine....between Fact and Service table.

Which i means when i select specific service i get specific result value from fact. As soon as include result table its not working.

Thanks

[damit23183]

Hi,

THis is the final framework i am working where all filters are coming from respective tables. However, the values in all 3 tables are based on CASE STATEMENT.  Please see framework here then will explain in detail;

Now, as you can when i select any of filter above the result should reflect on SCORE column only from table below;

Let me explain first about table;

I am pulling thsi table from Sharepoint where client has created spread sheet. The reason behind this is client can change TARGET, THRESHOLD and WEIGHT column's value any time. However KPI name will be as it is. 

Therefore i have used SWITCH function to put correct value in front of correct KPI. Furhter, all values for respective KPI are coming from different different table.

Using this SWITHC formula;

Score = SWITCH(TRUE(),'Customer Experience'[Metric Name]="End to End TAT by Service",AVERAGE(Fac_Incident[Fac_Incident.ResolutionTTR%]),                         

    'Customer Experience'[Metric Name]="ADO Backlog by Service - Volume by Enhance",[No. of ADO by Enhance],                                                                       

'Customer Experience'[Metric Name]= "ADO Backlog by Service - Volume by Sustain",[No. of ADO by Sustain])

Now final result should like below; for example if i choose one service call AAMI then score value in front highlighted row should .87 which is correct.

I am doing this QA on side with comparing data with client database.

Please help me i am totally block here as drilldown is not working.

Thanks

[d_gosbell]

---

@damit23183 wrote:

Now final result should like below; for example if i choose one service call AAMI then score value in front highlighted row should .87 which is correct.

So if I understand you correctly are you saying that everything you are showing us in the screenshot is working how you expect...

---

Please help me i am totally block here as drilldown is not working.

---

What do you mean by "drilldown" are you talking about  using the "drillthrough" feature to navigate to another page or something else entirely?

What do you mean by "is not working"? Are you getting an error? Are the results not what you expect? If the results are different from what you expect what results are you expecting and what ones are you actually seeing?

[Greg_Deckler]

Not sure if I am missing something here but that should literally be able to be accomplished using the default Average aggregation in a table. Are you saying that you want the first table displayed exactly like (no aggregation on %Value) that but once something is selected you do want the average? If that is the case, create a measure that uses HASONEVALUE as an IF check and if there is one value, return the Average, otherwise, use a MAXX on the FILTER of your table where the [ID] matches.

[d_gosbell]

You could use an expression like the following to ignore all filters on the table, except for the Service Name

Avg = CALCULATE(average('Table'[%Value]), ALL(), VALUES('Table'[Service Name]))

[damit23183]

Thank you for responding back.

Your formula is usefull but some how drilldown option is not working. 

For example: when i choose service A i should only see value from Service A. 

Looks like its little complicated. Let me send full explanation with screen shot.

Thanks