topic Re: Adding calculated measure in Connectivity & Data Prep

Adding calculated measure

jacek27031 — Thu, 25 Nov 2021 18:52:19 GMT

Hi,

I have 2 tables:

In the first one (SO Table) I have sales in units [# Sales Out Quantity]. The second table (Pricelist) is the price list in which I have recommended prices for each product ID [SRP]. Of course there is an association between both tables by %productID.

How to add a measure that shows sales in value terms and calculate productID.[# Sales Out Quantity] * productID[SRP] ?

Thanks in advance for any help.

Re: Adding calculated measure

Or — Thu, 25 Nov 2021 22:42:17 GMT

You could either join these two tables into a single table by using JOIN / LEFT JOIN / CONCATENATE and then perform the calculation on the ensuing table, or you could create a mapping load to fetch the price into the SO Table and perform the calculation there, or you could just perform the calculation on the front end in a master measure or chart measure.

Re: Adding calculated measure

jacek27031 — Fri, 26 Nov 2021 09:40:38 GMT

Sth like this?

m: mapping LOAD "%Product ID", SRP
FROM [lib://PL_CONS_TRACKER (len_jwoz)/Pricelist.qvd];

ORDERPOSITION:
LOAD
"%Product ID",
"Product ID",
%Date,
"%Customer ID",
"Customer ID",
"# Sales Out Quantity",
(SRP)*ApplyMap('m', "# Sales Out Quantity", 0) as revenue
FROM [lib://bms3_emea_root_folder/qvd_facts/f_WESI_SO.qvd](qvd)

Re: Adding calculated measure

Or — Fri, 26 Nov 2021 14:45:44 GMT

I think you're using ApplyMap on the wrong side here... should be something like:

"# Sales Out Quantity"*ApplyMap('m', "%Product ID", 0) as revenue