PowerPivot: Complicated aggregation

[Mer333]

Hi everyone!

I just stuck against an unusual task...

I have a table like:

[table="width: 500, class: grid"]
[tr]
[td]Market[/td]
[td]Category[/td]
[td]Week[/td]
[td]Amount of SKU[/td]
[/tr]
[tr]
[td]US[/td]
[td]Cars[/td]
[td]5[/td]
[td]1[/td]
[/tr]
[tr]
[td]Africa[/td]
[td]Cars[/td]
[td]7[/td]
[td]3[/td]
[/tr]
[/table]

So the problem here is that I need an aggregation where:
1) SUM Amount of SKU inside every week and every market.
2) AVERAGE First step for every category.

Any ideas? I tried some "X" functions but didn't succeed yet.

 

[scottsen]

You were definately on the right track w/ X functions. You are looking for someting like:
=AVERAGEX(VALUES(Calendar[WeekNumber]), [Amount of SKU])

But it's really hard to say exactly without seeing more of the tables/model. But AverageX is totally your guy.

 

[Mer333]

The problem is that I need to Average of SUM of Amount SKU of unique market + week.

 

[Mer333]

OMG... It seems I did it... I will continue investigate but at first sight it works!

AVERAGEX(VALUES(Table[Market]),AVERAGEX(VALUES(Table[Week]),SUM(Table[Amount of SKU])))

 

[Mer333]

Very interesting clarification...

SUM(Table[Amount of SKU]) should be a separated measure... or it doesn't work.

Anyone knows why?