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
- v-juanli-msft
Community Support
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