Fixing Target values filtered by specific category selections

[Dougdutra]

Hi!

So I have 4 different Tables, each having columns that contain the yearly KPI Target values, in percentage, for different categories (Company -> 0, Country -> 1, Region -> 2, Business Unit -> 3).

Currently, I have a dashboard that allows the user to select specific Business Units, as well as their working Region and Country, all returning specific pre-defined Target values for each of these categories, when single-selected.

But I wanna leave the slicer open for multiple selections which helps the user to compare any other selection that they want.

But that's when the problem starts. My current measure is returning an average of the Target of the current selections that in some cases, when selecting multiple categories, bring no analytical info at all, and just creates confusion

So I made a schema on Excel of the logic that im thinking of to fix the Target

This is the measure I made trying to fix it, but it didn't really work:

It seems quite messy, I feel like i'll need a different approach

Any ideas on how to solve it? To return the Targets according to the slicer selection, like the Rules in the excel?

[daXtreme]

Hi @Dougdutra 

Can you please paste (as text) some example data for the 4 tables so that it can EASILY be copied? Also, these 4 tables must somehow be logically connected... If we treat them as dimensions, what is the fact table here? Not every company can be found in every region, I suppose, and not every region can be found in every country...

By the way, the measure you're asking for will be very specific and tightly bound to the hierarchy (a small change to the hierarchy will immediately call for a change to the measure). On another note, your rules are not easily understandable. For instance, what does it mean "No selection -> Company Target"? If there are many companies (as I believe should be the case), then which company are we talking about? If you don't select anything, then all companies are visible...