Creating a KPI across non related tables - Interesting problem!

[scaramangamp]

Hi all!

I'm new to this forum but had a look through and couldn't find an answer to this questions which I've been working on for a couple of days.

I have two distinct sets of data: Repair data and sales data. I want to find out the % of our repairs that happen when the product is off a particular age and then be able to drilll down.

Repair Data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Shop[/TD]
[TD]Component[/TD]
[TD]Repair Date[/TD]
[TD]Component Age at time of repair (months)[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]London[/TD]
[TD]TV[/TD]
[TD]14/05/2015[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Liverpool[/TD]
[TD]Fridge[/TD]
[TD]12/06/2015[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]New York[/TD]
[TD]Radio[/TD]
[TD]5/04/2015[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Washington[/TD]
[TD]TV[/TD]
[TD]15/05/2015[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Sales Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Shop[/TD]
[TD]Component[/TD]
[TD]Time since sale (months)[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Liverpool[/TD]
[TD]TV[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Whashington[/TD]
[TD]TV[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]London[/TD]
[TD]Fridge[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]London[/TD]
[TD]Radio[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]New York[/TD]
[TD]Fridge[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

The main KPI I want to make is: (Number of products repaired at each age / Number products that could have failed at that age). For example, if we are looking at a repair age of two months the denominator would be the number of products 2 months or older (i.e. they had the chance to fail at that age.)
I would like to present it in a graph (y axis) is % failed, x axis is age in months. I would then like to be able to have slicers for country, shop, component and a timeline for repair date (so I can see if this is improving over time).

I am playing around with DAX formulae but am struggling a little as I don't see how I can introduce the 'age in months' selection as a variable into the calculated field in the sales table.

It is quite a interesting challenge, I think, and I would really appreciate any help.

Thanks!

 

[Rijnsent]

Hi Scaramangamp,
I've been playing around with your data a little, but had a hard time with your month-approach. The easiest way I found around:
-add a column SALES_MONTH to both your tables (you can calculate that one for both) and use that for your x-asis, it makes comparisons a whole lot easier.
-Next, I would "merge" both tables into 1 (just under eachother), adding one column "REPAIR" or "SALES".
-If you have it all in 1 table, you can control all data with the same slicers (like so: http://blog.contextures.com/archive...ultiple-pivot-tables-with-excel-2010-slicers/ ), as it's otherwise a bit of VBA-programming you'll need to apply the same filters/slicers on non-related tables.
-for every SALES_MONTH it's then pretty easy to calculate the number of REPAIR/SALE and to filter out the "not yet relevant sales months".

Hope that gets you started,
Koen