Expression for daily percentages

[peterderrington]

Hi, 

I have a chart that shows a percentage over a date dimension. The expression I'm using is this - num(Sum([OWN CLOTHING])/sum([No. PATIENT ON WARD]),'#,##0%')

The end result looks like this:

Which is great but now i'm trying to create a bar chart version which uses the Day dimension and am having problems creating the expression to show the average on a daily perspective.

I've tried this expression - Median(Total Aggr(num(Sum([HOSPITAL WEAR])/sum([No. PATIENT ON WARD]),'#,##0%'),Day)) but it shows the same figure for every day and the result looks like this:

Any ideas?

Thanks

[sergio0592]

Hello,

With the field WeekDay in your load statement, try with:

avg(TOTAL <WeekDay> Sum([OWN CLOTHING])/sum([No. PATIENT ON WARD]))

[peterderrington]

Thanks for the suggestion Sergio, i can add "Load Weekday" into the Load statement but where should the "From" direct to?, Without it, it doesn't the field and your suggested solution doesn't work.

[sergio0592]

Hi, what do you mean with the From?

[peterderrington]

I mean in a load statement it normally looks like this:

LOAD Date,
Day,
[No. PATIENT ON WARD],
.....loads more fields here......
[Total (Activity Champ)],
[Total (Other Staff)]
FROM
[C:\Users\hrpde\Desktop\Active Ward Data.xlsx]
(ooxml, embedded labels);

It needs to know where its loading things from. I've tried without a FROM and the field and expression you suggested doesnt work, leading me to think i'm missing something

[sergio0592]

Ok, your mean in the preceding load. So, in your exemple, try with :

LOAD Date,
Day,
WeekDay(Day) as WeekDay,
[No. PATIENT ON WARD],
.....loads more fields here......
[Total (Activity Champ)],
[Total (Other Staff)]
FROM
[C:\Users\hrpde\Desktop\Active Ward Data.xlsx]
(ooxml, embedded labels);

[peterderrington]

Nope it doesn't like that

[sergio0592]

Strange, for me it works. Can you share your qvw?