Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Thanks
Solved! Go to Solution.
@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]
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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]
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group