Dimension based on multiple criteria / values in other dimensions

[Anonymous]

Hello everybody,

I want to create a New dimension called  Freight TYPE  with two values : Dedicated and MIXED.

Dedicated freight  is any freight with only one type of Customer Group under Section TYPE
“ OUT”  , so that: Freight 1: would be classified as mixed and Freight 2 as dedicated.

The end result would be:

Customer
Group              Freight.TYPE        Cost

A                     Mixed                       300    

A                   Dedicated                   200

Data looks somewhat like below:

Freight #             Section.Type                     Customer Group1            COST

1                                             OUT                                      A                     50

1 OUT B 100

1                                             BH                                         X                     150

2                                             OUT                                      A                      100   

2                                             BH                                         Z                      100      

I was  trying different ways in calculated dimension combaining  AGGR, TOTAL, Count(Distinct) and iF

But none of it worked.

Big thanks in advance to anyone who can solve it !

[#2]

look on v1

I changed a little your oryginal field names. This is why sharing sample is the best option.

let me know if it helps.

pozdrawiam serdecznie

Darek

[Anonymous]

Sudeep,

Big Thanks  for your help !!!

Your solution  works great