
Re: Multiple Dimension aggregation Columns
omar bensalem Jan 24, 2018 6:18 PM (in response to vidya sagar malla)So basically:
to have the first 3 products by sales amout:
Sum({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Sales Amount])
to have the first 3 products by actual amount:
Sum({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Actual Amount])
so :
to have the avg of risk value depends on the top 3 based on sales amount:
Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])
to have the avg of risk value depends on the top 3 based on actual amount:
Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])
if u want to have this in a table:
as dimension:
=ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')
as a measure:
if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =
'Average of TOP 3 Products by Sales Amounts', Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),
if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =
'Average of TOP 3 Products by Actual Amounts',Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])
))
result:

Re: Multiple Dimension aggregation Columns
vidya sagar malla Jan 24, 2018 8:31 PM (in response to omar bensalem )Hi Omar,
You are really a genius. I really appreciate your help today. The expression you shared worked as expected. One last thing i would like to share is. I also want to include my own products example: If(wildmatch([Product],'*Micro*'),[Product]). Is there a way that I can add this if condition to the valuelist?
Thanks,
Vidya

Re: Multiple Dimension aggregation Columns
omar bensalem Jan 26, 2018 5:30 AM (in response to vidya sagar malla)If I fully understand ur request:
Let's say you have 1000 products, but only 50 that contains Micro, and u want to to do the Avg for the top 3 of these 50?
Is that so?
If yes, u change ur expression from:
Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])
to
Avg({<Product={"=rank(sum( {<Product={=wildmatch([Product],'*Micro*')=1}>} [Sales Amount]),Product)<=3"}>}[Risk Value])
so: the final expressions would be:
as dimension:
=ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')
as a measure:
if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =
'Average of TOP 3 Products by Sales Amounts',
Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Sales Amount]),Product)<=3"}>}[Risk Value]),
if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =
'Average of TOP 3 Products by Actual Amounts',
Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Actual Amount]),Product)<=3"}>}[Risk Value])
))

Re: Multiple Dimension aggregation Columns
vidya sagar malla Jan 26, 2018 12:51 PM (in response to omar bensalem )Hi Omar,
I found the solution for this. let's follow the order.
 I have 1000 Products, But only 50 that contains Micro. Now I want to show Avg risk value of top 3 by sales amount of 1000 Products and individual Riks values of products that contains Micro.
Example:
Micro1 10.20
Mirco2 12.36
 Solution to achieve this requirement is.
load * inline [
Dim
1
2
3
4
5
6
7
8
9
10
];
Dimension:
=pick(Dim,'Average of TOP N Products by AUM: 1 YR Risk and Returns','Average of TOP N Products by 1 YR Flows: 1 YR Risk and Returns','Average of Bottom N Products by 1 YR Flows: 1 YR Risk and Returns',
if(WildMatch([Firm Name],'*Mirco*'),[Product Name]))
Measure:
=
PICK(Dim, Avg({<[Product ID]={"=rank(sum([Sales Amount]),[Product ID])<=10"}>}[Risk Value]),
Avg({<[Product ID]={"=rank(sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),
Avg({<[Product ID]={"=rank(sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),
([Risk Value])
)
Thanks,
Vidya


