Actual Vs. Target in percentage

[sogloqlik]

Hi all.

We have a need to show actual scrap percentage vs. target.
The issue is that the targets are in percentage.
Attached are two files
The actual for product per date (The actual Percentage is just Scrap/Qty)
The targets - Each product is being produces in a line which belongs to a department. the target is for a product.

How can I show it per product/line/department/total?

Thx.

[justISO]

Hi, when you load your data from these 2 excels, rename 'product' field form 'Targets' to 'Product', to make relation between these 2 tables and convert your 'Target' (which is in percentage) to number. Like:

production:
LOAD
    [Scrap(k"g)],
    [Qty(K"g)],
    Product,
    "Date"
FROM [lib://AttachedFiles/Production.xlsx]
(ooxml, embedded labels, table is גיליון1);

target:
LOAD
    product as Product,
    "line",
    department,
    num(Target) as Target
FROM [lib://AttachedFiles/Targets.xlsx]
(ooxml, embedded labels, table is גיליון1);

So your data model will look like this:

In report level just add your desired fields and 2 measures, 'Scrap/Qty' and 'Target'. Format both to number with percentage. You have your target vs production:

[sogloqlik]

Hi.

Thx for your replay. the problem is not when you present it in a strait table with all the dimensions but when you must sum it in a department level (without the product) or even above.

[justISO]

As you don't have targets per line/department/product level, closest to correct solution would be take average of targets, I guess. So you take Avg(Target) as Target and aggregated production as actual

avg(aggr( sum([Scrap(k"g)])/sum([Qty(K"g)]) ,Product))