Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
Greetings of the day !.
I need some help to calculate the average formula with below mentioned output format.
I tried like this ; avg(aggr(sum(amount),[week]))
Please find the attached sample data.
Input Data :
ID | Week | amount |
1 | 37 | 5500 |
1 | 38 | 20000 |
1 | 45 | 13000 |
1 | 47 | 24000 |
1 | 50 | 24000 |
5 | 40 | 15000 |
5 | 44 | 17000 |
10 | 43 | 50000 |
12 | 46 | 80000 |
12 | 47 | 70000 |
Week is my dimension.I taken in Pivot table for to fulfil in horizontal way.
Out put :
ID | Week | 37 | 38 | 40 | 43 | 44 | 45 | 46 | 47 | 50 | Grand Total | Average |
1 | 5500 | 20000 | 13000 | 24000 | 24000 | 86500 | 17300 | |||||
5 | 15000 | 17000 | 32000 | 16000 | ||||||||
10 | 50000 | 50000 | 50000 | |||||||||
12 | 80000 | 70000 | 150000 | 75000 | ||||||||
Total | 5500 | 20000 | 15000 | 50000 | 17000 | 13000 | 80000 | 94000 | 24000 | 318500 |
More Thanks,
Ajay
This:
Script:
Table:
LOAD ID,
Week,
amount
FROM
Avg_Help.xlsx
(ooxml, embedded labels, table is [Data ]);
Dim:
LOAD * Inline [
Dim
1
2
];
Pivot Table
Dimensions:
ID
=Pick(Dim, Week, 'Total')
Dimension Color Expression: If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))
Dimension Text Format: =If(Pick(Dim, Week, 'Total') = 'Total', '<B>')
Expression:
=If(Pick(Dim, Week, 'Total') = 'Total', Sum(DISTINCT Aggr(NODISTINCT Sum(amount), ID)), Avg(Aggr(Sum(amount), ID, Week)))
Background color expression:
If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))
Credit to Kush141087 for introducing this Pick technique to me.
Best,
Sunny
UPDATE:
Sort Expression for calculated Dimension:
=Pick(Dim, Week, 'Total') with Descending Sort
I think you may need to add ID to your aggr() dimensions:
avg(aggr(sum(amount),[week], ID))
This?
Expression: =Avg(Aggr(Sum(amount), ID, Week))
Thanks for quick reply....But its not giving expected output.
I want average also...Upto grand total i got it.
PFA..
You want average and grand total both?
Yes....I want Grand total and Average last right side.
Your earlier output is right but i need grand total also...Your average is perfect.
This:
Script:
Table:
LOAD ID,
Week,
amount
FROM
Avg_Help.xlsx
(ooxml, embedded labels, table is [Data ]);
Dim:
LOAD * Inline [
Dim
1
2
];
Pivot Table
Dimensions:
ID
=Pick(Dim, Week, 'Total')
Dimension Color Expression: If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))
Dimension Text Format: =If(Pick(Dim, Week, 'Total') = 'Total', '<B>')
Expression:
=If(Pick(Dim, Week, 'Total') = 'Total', Sum(DISTINCT Aggr(NODISTINCT Sum(amount), ID)), Avg(Aggr(Sum(amount), ID, Week)))
Background color expression:
If(Pick(Dim, Week, 'Total') = 'Total', RGB(245,245,245))
Credit to Kush141087 for introducing this Pick technique to me.
Best,
Sunny
UPDATE:
Sort Expression for calculated Dimension:
=Pick(Dim, Week, 'Total') with Descending Sort
Hi,
May be try this..
Add one dummy dimension, and use the SecondaryDimensionality() in your expression..
Like..
=if(SecondaryDimensionality()=1,sum(aggr(sum(amount),ID,Week)),
if(SecondaryDimensionality()=0,Avg(Aggr(sum(amount),ID,Week)),(sum(amount))))
Thanks Sunny.Its working fine.
I need to understand the 'Dim'Technique.
Please explain this logic when you get time.
Thanks,
Ajay