Orders vs target when target is on higher level then orders

[Anonymous]

Hello,

I've created a model where I have Persons in a Team that can create orders. The teams have a target for the amount of orders, but the persons don't. 

When I create a visual that gives the amount of orders and the target per year, it seems to work. When I use the slicers on the team, the orders and targets are sliced correctly. 

But, when slices on Persons, the data is not correct. The orders are sliced correct, but the target stays at the level of the teams. 

So, with the data form the example attached:

Orders 2018: 104 (42 for team 1 and 62 for team 2)

Target 2018: 81 (29 for team 1 and 52 for team 2)

When Sliced on team, the above numbers are correct. When sliced on Person, target doesn't change (try slicing Z.Z., target stay at 81). 

Desired output:

Don't know for sure, but a desired output could be that the line dissapears from the visual (i.e. target = 0 when sliced on person).

Other ideas for output are also welcome.

P.s. in the real data I have targets on months, 25 teams and more than 100 persons. So thats makes a lot of rows.

Example data

https://1drv.ms/u/s!AvC8sXOgGhuRqXIsCmHe2xKYd7ud?e=Tm9iZG

[TomMartens · Accepted solution]

Hey @Anonymous ,

you can create a measure like this:

_Target = 
IF(
    ISFILTERED('Person'[Person])
    , BLANK()
    , CALCULATE(SUM('Target'[Target]))
)

and use this measure for the line chart.

Maybe this article provides some additional insights on how to handle different granularities:

https://www.daxpatterns.com/handling-different-granularities/

Hopefully, this provides some insights and new ideas on how to tackle your challenge.

Regards,

Tom

[Fowmy · Accepted solution]

@Anonymous 

You can hide the line when you select a value in the Persons column by replacing your Target in the visual with the following measure:
I suggest you split the target by person based on your business logic, then you will have orders and target per person in the same visual.

Target_M = 

IF(
    ISFILTERED(Person[Person]),
        BLANK(),
        SUM(Target[Target])
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[TomMartens · Accepted solution]

Hey @Anonymous ,

you can create a measure like this:

_Target = 
IF(
    ISFILTERED('Person'[Person])
    , BLANK()
    , CALCULATE(SUM('Target'[Target]))
)

and use this measure for the line chart.

Maybe this article provides some additional insights on how to handle different granularities:

https://www.daxpatterns.com/handling-different-granularities/

Hopefully, this provides some insights and new ideas on how to tackle your challenge.

Regards,

Tom

[Anonymous]

Thanks!!

It was easier then I thought 🙂