Create measure to calculate target

[Anonymous]

Hello,

I have a targets table for which I want to use to implement a bit of complicated logic for displaying targets. A sample of the table below. Each country has a number of products and customers. I want the target value to be displayed based on my selections. For example in the below. If I select nothing, the target should be 100 as it is the highest level in the hierarchy and it is the country target, if I select product1 it will show the target for PRODUCT1 and same thing if I select PRODUCT2.

Date	Country	Product	Customer	Country_Hierarchy_Depth	Product_Hierarchy_Depth	Customer_Hierarchy_Depth	Target
Sep-23	US	ALL	ALL	2	2	2	100
Sep-23	US	PRODUCT1	ALL	2	1	2	150
Sep-23	US	PRODUCT2	ALL	2	1	2	150
Sep-23	US	PRODUCT3	ALL	2	1	2	150
Sep-23	US	PRODUCT1	STORE	2	1	1	120
Sep-23	US	PRODUCT2	STORE	2	1	1	120
Sep-23	US	PRODUCT3	STORE	2	1	1	120

Now in case I select PRODUCT1 and PRODUCT2 together, it should show me the target for the country which is 100 (and not for example the min and max of either products). Same thing if I select PRODUCT1 and PRODUCT2 and STORE, I need to see the total country also (100), but of course if I select PRODUCT1 and STORE it will automatically be 120.

I also created a calculated column to sum the hierarchy depths of the 3 columns (if this could be of any help), tried using it with MAX function but didnt get anywhere.

Any help on how I can tackle this?

Thank you!!

[amitchandak · Accepted solution]

@Anonymous , Create a disconnected table with

Prd = distinct( Table[Product])

Then create a measure

M1=

var _sel  = calculate(isfiltered(Prd[Product]), allselected())

return

if(_sel, calculate(Sum(Table[Target]), filter(Table, Table[Proudct] in values(Prd[Product]))) , calculate(Sum(Table[Target]), filter(Table, Table[Proudct] ="All")))

[Anonymous]

that did it! thanks a lot