Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jct999
Advocate II
Advocate II

Creating measure that ignores some rows depending of other rows

Hello All,

I have the followong dataset : 

COUNTRYBRANDKPIPRIORITYQTY

COUNTRY_1

BRAND_A

SALESHIGH100
COUNTRY_1BRAND_BSALESHIGH10
COUNTRY_1

BRAND_C

SALESLOW45
COUNTRY_1 TARGET 800
COUNTRY_2BRAND_ASALESHIGH205
COUNTRY_2BRAND_BSALESHIGH78
COUNTRY_2BRAND_CSALESHIGH1
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 : 

BRANDTarget per brandRemarks
BRAND_A1500

 = 800 + 700

BRAND_B1500

 = 800 + 700

BRAND_C1500

 = 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 : 

 

BRANDTarget per brand if high priorityRemarks
BRAND_A1500

 = 800 + 700

BRAND_B1500

 = 800 + 700

BRAND_C700
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

 

2 REPLIES 2
tamerj1
Super User
Super User

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]
    )
)

2.png

amitchandak
Super User
Super User

@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" )
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.