Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
6 years ago
Solved

Average formula

Hi,   Need help with AVERAGE formula,   I would like to calculate below condition,   ID                   ServiceName        %Value     --> Column Name 1                             A         ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi damit23183 ,

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

     

    Best Regards

    Rena

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

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's avatar
    d_gosbell
    Super User
    6 years ago
    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?