calculate Median Average in Straight Table

[peterderrington]

Hi, 

I have a dashboard that displays various different pieces of information.

I have some tabs that show various KPIs for departments including one that is a combo chart that displays the median average for compliance within a department.

The expression it uses is 

Median(TOTAL AGGR(Count({<Ward={'NSEC03'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC03'}, [Ward transferred to]-={'NSEC07'}>}Ward),[PAS Date]))

This works perfectly in the chart and displays a line showing an average of 50%

However i also have a tab that shows a straight table that i wish to show these averages in a league table sort of way.

I am able to get the other different elements to work but i cant work out the syntax for calculating the average.

I have tried:

Median(AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),FromCorrectedWard))

Which shows 41%

and 

Median(AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),[PAS Date]))

Which shows 8.2%

The first expression is closer to what i think the calculation needs to be whilst the second is closer to the original KPI expression.

Does anybody have any ideas?

Thanks.

[sunny_talwar]

Would you be able to share a sample to take a look at the issue?

[peterderrington]

So i can post these images if that would help?

Obviously the bottom image is the KPI graph with the correct Median Average showing and the top image is the new Straight Table i'm trying to create.

[sunny_talwar]

May be this

Median(TOTAL <Ward>  AGGR(Count({<Ward={'NSEC03'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC03'}, [Ward transferred to]-={'NSEC07'}>}Ward),[PAS Date], [Ward]))

[peterderrington]

But i need it to show the median average of each department, using yours it would only show me the median average for the one department.

[sunny_talwar · Accepted solution]

May bad, I mean this

Median(TOTAL <Ward>  AGGR(Count({<[Questions Complete] = {'Yes'}, [Ward transferred to] -= {'NSEC07'}>} Ward)/Count({<[Ward transferred to] -= {'NSEC07'}>} Ward), [PAS Date], [Ward]))

[peterderrington]

Ok, I’ll give it a try however in this calculation you’ve included the line that excludes a department (which as I type I realise my be part of the reason for the discrepancy).

I’ll check when I’m back in work.

Thanks.

[peterderrington]

Thank you, with a small alteration I've got it working:

Median(TOTAL <FromCorrectedWard> AGGR( Count({<[Questions Complete]={'Yes'}>}FromCorrectedWard)/Count(FromCorrectedWard),[PAS Date], FromCorrectedWard))