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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

SUMIFS with wildcard and many to many relationship

Hi Everyone,

 

I have two working tables (A & B) and the ideal transformed result table (C) below and attached in Excel/PBI format. I need to SUMIF the Balance in Table B based on the shared Code in Table A & B. The two complications are:

  • The values under Code in Table A include multiple wildcards some of which overlap
  • There is a many to many relationship between Code, the Primary Key in Table A and Code, the Foreign Key Code in Table B given these wildcards

 

Previously the SUMIF function was used in Excel (as indicated in the excel example). Any suggestions on how I can resolve this issue in Power BI would be greatly appreciated.

 

Sample Data:

Excel Sample and Transformation Example 

Power BI Sample and Tranformation Example 

 

Table A: KPIs, ~2k rows

Code (PK)

KPI Number

KPI Name

Department

E99??1?1

1

Total Sales

Overall

E99?B1?1

2

Total Sales

Body Shop

E99?D1?1

3

Total Sales

Pre-Delivery

E99?N1?1

4

Total Sales

New Vehicles

 

Table B: Balances, ~200k rows

Code (FK)

Balance

Country

E991B111

100

Australia

E991B121

200

USA

E991B131

300

Canada

E992B111

400

UK

E992B121

500

China

E992B131

100

Australia

E993B111

200

USA

E993B121

300

Canada

E993B131

400

UK

E991D111

500

China

E991D121

100

Australia

E991D131

200

USA

E992D111

300

Canada

E992D121

400

UK

E992D131

500

China

E993D111

100

Australia

E993D121

200

USA

E993D131

300

Canada

E991N111

400

UK

E991N121

500

China

E991N131

100

Australia

E992N111

200

USA

E992N121

300

Canada

E992N131

400

UK

E993N111

500

China

E993N121

100

Australia

E993N131

200

USA

 

Table C: Transformed Data, ~2k rows

Code (PK)

KPI Number

KPI Name

Department

Total

E99??1?1

1

Total Sales

Overall

7800

E99?B1?1

2

Total Sales

Body Shop

2500

E99?D1?1

3

Total Sales

Pre-Delivery

2600

E99?N1?1

4

Total Sales

New Vehicles

2700

 

I was able to find the post below which was quite similiar but didn't account for a many to many relationship between the tables:

https://community.powerbi.com/t5/Desktop/HOW-TO-SUMIFS-with-wildcards-and-exclusions/m-p/444715#M205...

 

Thanks

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@campbellmurphy 

You cannot create a valid relationship with wildcard values in the columns. I removed the relationship to avoid non-matching records pushing a blank row. Create a measure as follows. I have attached the file below my signature.

Measure = 
VAR __TEXT = SELECTEDVALUE('TABLE A: KPIs'[Code (PK)]) RETURN  
SUMX(
    ALL('TABLE B: Balances'),
    IF(
        SEARCH(
        __TEXT,
        'TABLE B: Balances'[Code (FK)],,0
        ) > 0 ,
        'TABLE B: Balances'[Balance]
    )
)

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@campbellmurphy 

You cannot create a valid relationship with wildcard values in the columns. I removed the relationship to avoid non-matching records pushing a blank row. Create a measure as follows. I have attached the file below my signature.

Measure = 
VAR __TEXT = SELECTEDVALUE('TABLE A: KPIs'[Code (PK)]) RETURN  
SUMX(
    ALL('TABLE B: Balances'),
    IF(
        SEARCH(
        __TEXT,
        'TABLE B: Balances'[Code (FK)],,0
        ) > 0 ,
        'TABLE B: Balances'[Balance]
    )
)

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors