Comparing fields in Set Analysis

[#1]

Hi all,

Hope you can help, I'm struggiling to be able to find the right code to do a simple field - field sum.

Essentially I have a selection of Customers that I want to subtract their values from the Benchmarks we have set to identify if they are up/down vs that benchmark

Each Benchmark and Customer/Product has a Value in a table

Product Name	ThreeMonth
Customer1	72.21%
Customer2	60.00%
Customer3	65.84%
Customer4	67.58%
Customer5	65.43%
Customer6	66.21%
Customer7	63.26%
Benchmark1	66.03%
Benchmark2	65.24%

And in another table I state which Benchmark relates to which customer

Product Name	Benchmark Name
Customer1	Benchmark1
Customer2	Benchmark1
Customer3	Benchmark1
Customer4	Benchmark2
Customer5	Benchmark2
Customer6	Benchmark2
Customer7	Benchmark2
Benchmark1	n/a
Benchmark2	n/a

For Customer 1 it is benchmarked against Benchmark 1, so the sum should be 72.1 - 66.03, however I'm unable to write the correct code to have it identify that Benchmark 1 is related to Customer1 in this way,

Please help

Many thanks

[sunny_talwar]

What is your desired output going to look like?

[#3]

Hi Sunny,

It will be shown as a % so 72.1 - 66.03 = 6.07%

Many thanks

[swuehl]

Maybe something like this if you have selected a single Product Name (Customer1)

=Only(ThreeMonth) - Only({<[Product Name] = p([Benchmark Name]) >} ThreeMonth)

[#5]

Hi Stefan,

Need to operate in a Table/Pivot with the Customer/Product Name showing and then the Measures alongside for each Product so no selections

Many thanks

Ryan

[sunny_talwar]

May be this

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth)))

[swuehl]

Try solving this in your data model:

Products:
LOAD [Product Name]
  //  [Benchmark Name]
FROM

(ooxml, embedded labels, table is Sheet1);


LinkTable:
LOAD [Product Name],
  [Product Name] as Product,
  'Product' as Type
  //  [Benchmark Name]
FROM

(ooxml, embedded labels, table is Sheet1)
WHERE [Benchmark Name] <> 'n/a';
LOAD [Product Name],
  [Benchmark Name] as Product,
  'Benchmark' as Type
  //  [Benchmark Name]
FROM

(ooxml, embedded labels, table is Sheet1)
WHERE [Benchmark Name] <> 'n/a';




Fact:
LOAD [Product Name] as Product,
    ThreeMonth
FROM

(ooxml, embedded labels, table is Sheet2);

Product Name	Sum({<Type = {'Product'}>} ThreeMonth) - Sum({<Type = {'Benchmark'}>}ThreeMonth)
Customer1	6,2%
Customer2	-6,0%
Customer3	-0,2%
Customer4	2,3%
Customer5	0,2%
Customer6	1,0%
Customer7	-2,0%

[swuehl]

And if you change the last LOAD to:

Fact:
CROSSTABLE (MeasureType, Measure)
LOAD [Product Name] as Product,
    ThreeMonth
FROM

(ooxml, embedded labels, table is Sheet2);

You can create a pivot with one expression, showing MeasureType as second dimension:

Product Name	MeasureType	ThreeMonth
Customer1		6,2%
Customer2		-6,0%
Customer3		-0,2%
Customer4		2,3%
Customer5		0,2%
Customer6		1,0%
Customer7		-2,0%

[sunny_talwar]

Motivated by Stefan, putting down the expression based solution in case you don't want to make changes to script

ThreeMonth-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth))

RangeSum(ThreeMonth,

-Pick(Match([Benchmark Name], 'Benchmark1', 'Benchmark2'), Only(TOTAL {<[Product Name] = {'Benchmark1'}>} ThreeMonth), Only(TOTAL {<[Product Name] = {'Benchmark2'}>} ThreeMonth)))

[Kushal_Chawda]

Data:
LOAD [Product Name], 
     ThreeMonth
FROM
[Example (1).xlsx]
(ooxml, embedded labels, table is Sheet2);


Left Join(Data)
LOAD [Product Name], 
     [Benchmark Name]
FROM
[Example (1).xlsx]
(ooxml, embedded labels, table is Sheet1);


Left Join(Data)
LOAD Distinct [Product Name] as [Benchmark Name],
     ThreeMonth as ThreeMonthBenchMark
Resident Data;


Final:
NoConcatenate
LOAD *,
     num(ThreeMonth-ThreeMonthBenchMark,'#0.00%') as Sum
Resident Data;


DROP Table Data;