Problem grouping measure on merged dimension level

[former_member598675]

Hi all,

I have a problem grouping a measure on level of a merged dimension in a WebI Report. We're on Webi 4.1.

The Setup is the following:

My report has 2 queries (BEx Query), one one sales data where I get a company region, ship-to country and the revenue and the other one on shipping days per month on country level. I want to calculate a daily average based on the shipping days ([revenue]/[shipping days date]). As long as I am on country level everything is fine, but I also need it on company region level.

Query 1: Sales
[Company Region]
[Ship-to Country]
[revenue]

Query 2: Shippingdays
[Country]
[shipping days date]
[shipping days month]

Merged dimension [country] - [ship-to country]
A [company region] can have multiple [ship-to country] and one [ship-to country] can be assigned to multiple [company regions].

When I do my table like

Code:[company region] | [country] | [shipping days date]
Northern Europe | GB | 2
Northern Europe | IE | 2
Northern Europe | DK | 3
Central Europe | DE | 2
Central Europe | GB | 2

I get all the correct values per country. However, when I want to group on [company region] or just do a break on [company region] level it doesnt work, I always get the sum/average for the whole query in evey row.

What I need is a table like

Code:[company region] | Average([shipping days date])
Northern Europe | 2,33
Central Europe | 2

Goal is to have an average of shipping days over all countries within a company region for the daily average revenue calculation.

Any ideas on how to aggregate the shipping days on the company region level? It should be possible I'd say as the assignment can be made on country level. I just would need to bring the according shipping day measure per country to each country within a region from the other query and build an average there.

Hope that makes sense somehow. Thanks for you help

[former_member598675]

Thanks Amit Kumar,

when I create a dimension variable for shipping days I get #datasync error as soon as i put [company region] into the table

[amitrathi239]

you have to create shipping days and Company region as detail variables and associate them with merged dimension object.

[amitrathi239]

follow below steps.

Create dimension variable for Shipping days date if it's measure object.

Final variable for average=Average([V Shipping days date] ForEach ([country]))