Creating measure that ignores some rows depending of other rows

[jct999]

Hello All,

I have the followong dataset : 

COUNTRY	BRAND	KPI	PRIORITY	QTY
COUNTRY_1	BRAND_A	SALES	HIGH	100
COUNTRY_1	BRAND_B	SALES	HIGH	10
COUNTRY_1	BRAND_C	SALES	LOW	45
COUNTRY_1		TARGET		800
COUNTRY_2	BRAND_A	SALES	HIGH	205
COUNTRY_2	BRAND_B	SALES	HIGH	78
COUNTRY_2	BRAND_C	SALES	HIGH	1
COUNTRY_2		TARGET		700

1) I defined a measure that calculate the Target per brand : Target per brand is sum of the volumes for rows whose KPI=TARGET, all brands.

Target per brand = CALCULATE (
            SUM ( 'DATASET'[QTY] ),
            ALL ( 'DATASET'[BRAND] ),
            'DATASET'[KPI] = "TARGET"
        )

It works fine : 

BRAND	Target per brand	Remarks
BRAND_A	1500	= 800 + 700
BRAND_B	1500	= 800 + 700
BRAND_C	1500	= 800 + 700

2) Now I want to create a similar measure that calculate the Target per brand if Priority is set to High for the brand in the country.
The result should be like this : 

BRAND	Target per brand if high priority	Remarks
BRAND_A	1500	= 800 + 700
BRAND_B	1500	= 800 + 700
BRAND_C	700	The priority of the BRAND_C is LOW for the COUNTRY_1, so the TARGET qty for COUNTRY_1 is ignored when calculating the measure.

I don't know how to write this measure :o( 
Can any boby help me to write it ?

I thank you.

JC

[tamerj1]

Hi @jct999 
You can use this code

Target per brand (high priority) = 
SUMX (
    ADDCOLUMNS ( 
        Table1, 
        "@Target", 
            CALCULATE ( 
                SUM ( Table1[QTY] ),
                ALLEXCEPT ( Table1,  Table1[COUNTRY] ), 
                Table1[KPI] = "TARGET"
            )
    ),
    IF ( 
        Table1[PRIORITY] = "HIGH",
        [@Target]
    )
)

[amitchandak]

@jct999 , Try if this can help

Target per brand = CALCULATE (
SUM ( 'DATASET'[QTY] ),
filter( ALL ( 'DATASET'), 'DATASET'[KPI] = "TARGET" && 'DATASET'[COUNTRY] = max('DATASET'[COUNTRY]) && [PRIORITY] <> "LOW" )
)