Forum Discussion
sumif and filter on two different tables
Hello!
I am new to Power BI and would really appreciate you help on the following question:
I have two tables: Registration Status (Approved, Declined, In Process), and Pipeline (Projected Sales Amount). I am looking for sum of sales amount for each registration status. Thank you!
| Registration Status | |
| Opp ID | Registration Status |
| 123456 | Approved |
| 234567 | Approved |
| 345678 | Approved |
| 456789 | Approved |
| 654321 | Declined |
| 765432 | Declined |
| 876543 | In Process |
| 987654 | In Process |
| Pipeline | |
| Opp ID | Amount |
| 123456 | 500 |
| 234567 | 600 |
| 345678 | 700 |
| 456789 | 800 |
| 654321 | 900 |
| 765432 | 100 |
| 876543 | 300 |
| 987654 | 200 |
8 Replies
ceebuAdvocate I
WHen I tried using the same data, PowerBI automatically detected the relationships between these two tables. The resulting table provides what you want (right hand side)
Thank you Ceebu. I realized what the problem was. The amount field wasn't a direct value in the pipeline table, but a key measure based on a calculation of two fields in the pipeline table. It seems I can't manage the relationships between Registration Table and Key Measures, and when I try to drag amount to different registration status, the amount shows as a lump sum value of all the registration status rather than individual break down of each status.
What should I do in this case? Thank you!
- ceebu
I am not able to understand the requirement clearly - If you could securely share the sample data, I can try to find a way.