Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Anonymous
I get different percentages but I'm not sure whether it is because I've understood incorrectly what you need or because you made a mistake in the numbers you show. In any case, try this (see it all at work in the attached file)
1. Create this measure:
MeasureCount = COUNT(Table2[Customer Name])2. Create this other measure based on the previous one:
MeasureTotal = DIVIDE ( [MeasureCount]; CALCULATE ( SUM ( Table1[Volume of Customers] ); FILTER ( Table1; Table1[YYYYMM] = SELECTEDVALUE ( Table2[YYYYMM] ) && Table1[Spend] = SELECTEDVALUE ( Table2[Spend] ) ) ) )
3. Place Table2[YYYYMM], Table[Spend] and Table2[Good Bought] in values of a table visual. Make sure they all are set to 'Don't summarize'.
4. Place both [MeasureCount] and [MesureTotal] in the table visual
Hi @Anonymous
I get different percentages but I'm not sure whether it is because I've understood incorrectly what you need or because you made a mistake in the numbers you show. In any case, try this (see it all at work in the attached file)
1. Create this measure:
MeasureCount = COUNT(Table2[Customer Name])2. Create this other measure based on the previous one:
MeasureTotal = DIVIDE ( [MeasureCount]; CALCULATE ( SUM ( Table1[Volume of Customers] ); FILTER ( Table1; Table1[YYYYMM] = SELECTEDVALUE ( Table2[YYYYMM] ) && Table1[Spend] = SELECTEDVALUE ( Table2[Spend] ) ) ) )
3. Place Table2[YYYYMM], Table[Spend] and Table2[Good Bought] in values of a table visual. Make sure they all are set to 'Don't summarize'.
4. Place both [MeasureCount] and [MesureTotal] in the table visual
Not all heroes wear capes...
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |