
1. Re: Comparing Inlist values of two dimensions
swaroop.gantela Jun 28, 2018 9:38 PM (in response to dinesh punnam)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
Please see workbook attached in Forum Thread.

274412compare.xlsx 11.2 KB

274412match.twbx 37.1 KB


2. Re: Comparing Inlist values of two dimensions
dinesh punnam Jun 29, 2018 7:32 AM (in response to swaroop.gantela)Swaroop,
Thanks for your response
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

Essentials comparing srings.twbx 37.0 MB


3. Re: Comparing Inlist values of two dimensions
swaroop.gantela Jun 29, 2018 10:14 PM (in response to dinesh punnam)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
Please see workbook attached in Forum Thread.
Will continue exploring for simpler way.

274412match2.twbx 35.3 MB
