Forum Discussion
Lookup against multiple columns
Hello,
I'm trying to find the most efficient way of using PowerBI desktop for reporting sales.
I've got 2 tables on sales data (sample data below) that I'm trying to find a link without using nested IF or Switch statements. In an excel, I could have had a series of IF and VLOOKUPs to get to the final customer and then a SUMIFS by Customer name to get to the final output.
Since I've got data running into nearly 200,000 rows in excel, I'm trying to get the most efficient way of solving this through PowerBI. I tried using nested IFs and LOOKUPVALUE but it didn't seem to be the most effective solution.
The biggest challenge I'm facing is to lookup the Customer name using either the Bill-To name or the Ship-To name.
Any help would be much appreciated.
Table 1: Customer data table
| Bill to customer name | Ship to customer name | Sale Amount |
| AAA | bbb | $100 |
| CCC | bbb | $100 |
| DDD | ddd | $500 |
Table 2: Customer master table
| Customer details | Customer name |
| AAA | Customer 1 |
| bbb | Customer 1 |
| CCC | Customer 1 |
| DDD | Customer 2 |
| ddd | Customer 2 |
Expected output:
| Customer name | Total Sale amount |
| Customer 1 | $200 |
| Customer 2 | $500 |
4 Replies
Hi BUC25
Try either of the following measures:
Sales = CALCULATE ( SUM ( customerdata[Sale Amount] ), KEEPFILTERS ( customerdata[Bill to customer name] IN VALUES ( customermaster[Customer details] ) || customerdata[Ship to customer name] IN VALUES ( customermaster[Customer details] ) ) ) Sales2 = CALCULATE ( SUM ( customerdata[Sale Amount] ), KEEPFILTERS ( TREATAS ( VALUES ( customermaster[Customer details] ), customerdata[Bill to customer name] ) ), KEEPFILTERS ( TREATAS ( VALUES ( customermaster[Customer details] ), customerdata[Ship to customer name] ) ) )Thank you for the prompt response.
Just as a follow up, what if instead of the sale amount, I'm supposed to classify the data into regions i.e. in my example, if I have to assign a region as Customer 1 = Asia and Customer 2 = Europe, without the sale values, can i be use the same logic with KEEPFILTERS and TREAT AS without the CALCULATE SUM?
