3 Replies Latest reply on Jun 29, 2018 10:14 PM by swaroop.gantela

# Comparing Inlist values of two dimensions

Hello Community,

I have two dimension ( Sales office and Region) Want to compare the values in the both dimension (please find attached excel) and show respective meaures - if there is nothing matching (ex.unites states is not in region so we want to see full sum of all values)

Thanks

Dinesh

• ###### 1. Re: Comparing Inlist values of two dimensions

Dinesh,

I'm not sure if this got there, but maybe it can give an idea.

This next conditional is specific to the sample set, will likely

need to be modified for specific cases.

Mark matches using:

IF LEFT([Sales Office],2)=LEFT([Region],2)

OR (LEFT([Sales Office],2)="Pa" AND LEFT([Region],2)="Ce")

THEN 1 ELSE 0 END

If no matches, then sum all Regions, otherwise

fix the match measure value to the Sales Office:

IF { FIXED [Sales Office]:SUM([Has Match])}=0

THEN { FIXED [Sales Office]:SUM([Measure])}

ELSE { FIXED [Sales Office]:SUM(

IF [Has Match]>0 THEN [Measure] ELSE 0 END)}

END

• ###### 2. Re: Comparing Inlist values of two dimensions

Swaroop,

I am able to get Has match calculation but cannot achieve below as the measure below is already coming out of LOD (please see CPM  in attached twbx,

IF { FIXED [Sales Office]:SUM([Has Match])}=0

THEN { FIXED [Sales Office]:SUM([Measure])}

ELSE { FIXED [Sales Office]:SUM(

IF [Has Match]>0 THEN [Measure] ELSE 0 END)}

END

Ia m trying to substitute [measure] with CPM but as it was already in LOD - I see aggregation over aggregation error

Thanks,

Dinesh

• ###### 3. Re: Comparing Inlist values of two dimensions

Dinesh,

That does complicate things.

I was able to get numbers that looked right,

but it took quite a few steps.

There is probably an easier way.

This is the new version, combining LODs with Table Calcs.

Probably call all be done in LOD, but was just trying to get

something working first:

IF SUM({ FIXED [Sales Office]:SUM([Has Match])})=0

THEN

IF FIRST()=0 THEN

WINDOW_SUM(SUM( { INCLUDE  [Sales Office]:[CPM]}))

END

ELSE IF SUM([Has Match])>0 THEN SUM({ INCLUDE  [Sales Office]:[CPM]}) ELSE 0 END

END

This calculation doesn't work well with Grand Totals,

and so I put them on a separate sheet.

There may be a better way to get the Grand Total:

Why Your Grand Total or Subtotal Isn't Working as Expected