Forum Discussion
Using 2 Tables to perform functions
Hi, I'm very new to Power Bi, so this might be an easy one for you.
I have a Table 1, which shows all customers entering a supermarket, on a given YYYYMM, and based on an irrelevant criteria, we classify them as a high spender or a low spender.
| YYYYMM | Spend | Volume of Customers |
| 201701 | High | 35 |
| 201701 | Low | 22 |
| 201702 | High | 28 |
| 201702 | Low | 24 |
| 201703 | High | 47 |
| 201703 | Low | 51 |
| 201704 | High | 33 |
| 201704 | Low | 31 |
I also have a Table 2, which shows all customers in the supermarket that have bought Apples. They are shown not as total number of customers but as individual entries for each customer.
| YYYYMM | Spend | Good Bought | Customer Name |
| 201701 | High | Apple | John |
| 201701 | Low | Apple | Martha |
| 201701 | Low | Apple | Michael |
| 201701 | Low | Apple | Jane |
| 201701 | Low | Apple | Moody |
| 201701 | High | Apple | Paul |
| 201701 | High | Apple | Colin |
I want to figure out how many customers bought Apples as a percentage of how many customers entered the Supermarket, classified as High or Low spenders.
This is the output I want:
| 201701 | High | Apples | 20% |
| 201701 | Low | Apples | 35% |
| 201702 | High | Apples | 15% |
| 201702 | Low | Apples | 45% |
I joined the two tables on YYYYMM, but when I do the following function:
Apples Pct = countrows(Table2)/sum(Table1[Volume of Customers])
I get the right aggregated percentage. But when I try inserting 'Good Bought' in Rows, my count for High and Low shows up the same as the Total value.
| YYYYMM | Spend | |
| 201704 | High | 64 |
| 201704 | Low | 64 |
| Total | 64 |
When I try building relationships for Spend between the 2 Tables, it gives me an error for ambiguity.
Any advice?
1 Reply
Hi Anonymous
In Table1, we can get the total volumn of customers per month as below
YYYYMM Spend Volume of Customers total per month 201701 High 35 35+22=57 201701 Low 22 35+22=57 201702 High 28 28+24=52 201702 Low 24 28+24=52 According to your screenshot 2, Then i can calculate volumn of customers who bought apple per month per category (hign/low)
YYYYMM Spend Good Bought Volume of Customers apple 201701 High Apple 3 201701 Low Apple 4 Finally, for this percentage
"how many customers bought Apples as a percentage of how many customers entered the Supermarket, classified as High or Low spenders"
I should calculate with this formula
YYYYMM Spend Good Bought percentage 201701 High Apple (3/57)*100% 201701 Low Apple (4/57)*100% Is my understanding right?
Best Regards
Maggie
