comparing measures of a dimension against that of a specific dimension value

[LloydM]

hi,

i'm trying to generate this pivot...

from this data...

i would like to compute for the variance as the qty of store1 and store2 must be equal to the qty of whse for each discount category. 

Sum({<store={'whse'}>} total <'discount'> qty) doesn't seem to work.

Thanks in advance.

[ogster1974 · Accepted solution]

Create a new measure in your data load using applymap.

DataTmp:
LOAD
[store],
[discount],
[qty]
FROM [lib://Community:DataFiles/comparing measures of a dimension.xlsx]
(ooxml, embedded labels, table is Sheet1);

Mapping
DiscountQtyMap:
Load
discount,
qty
Resident DataTmp
Where store='whse'
;

Noconcatenate
Data:
Load
*,
ApplyMap('DiscountQtyMap', discount,0) as "whse qty"
Resident DataTmp
;

Drop table DataTmp;

[LloydM]

Thanks... the problem is if there's a store that doesn't have the complete discount category...

the resulting pivot will be as follows...

if  this happens, i would like the regular to appear under store1 with the sum(qty) at zero and the sum(whse qty) will still be at 100.... i guess have to find a way to create the record for store1 discount regular with zero as the qty.  any ideas how this can be done?