cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
Super User IV
Super User IV

@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!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

1 REPLY 1
Super User IV
Super User IV

@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!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors