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.
Hello,
I have datamodel with two tables - table with sales (id, amount) and second table with incorrect payments (id, reason). Tables are connected by relationship on Id in both tables (in other words sales table contains all ids and incorrect payments has some ids from sales). I want to create sum of amounts from sales table, with excluded payments from incorrect payments table.
I know that I could make join in powerquery, but I would like to use measure instead. I have tried something like below, but it didn't work. Any ideas?
CALCULATE(SUM('sales'[amount]),ISBLANK('incorrect payments'[Id]))
Thank you
Solved! Go to Solution.
I made 2 dummy tables to replicate the story you have provided. This essentially lets you search sum the Sales[Amount] by filtering out rows where the Sales ID exists in the Incorrect Payments table.
Sales
Sales ID | Amount |
1 | $50 |
2 | $100 |
3 | $150 |
4 | $100 |
5 | $250 |
6 | $100 |
7 | $150 |
8 | $110 |
9 | $125 |
10 | $108 |
Incorrect Payment
Sales ID | Reason |
4 | Declined Payment |
8 | Invalid Payment Method |
9 | Declined Payment |
10 | Duplicate Payment |
Sales Excluding Failed Payment =
VAR _VariableTable =
CALCULATETABLE (
VALUES ( 'Incorrect Payments'[Sales ID] ),
ALLSELECTED ( 'Incorrect Payments' )
)
RETURN
SUMX (
FILTER ( ALLSELECTED ( Sales ), NOT ( Sales[Sales ID] ) IN _VariableTable ),
Sales[Amount]
)
I made 2 dummy tables to replicate the story you have provided. This essentially lets you search sum the Sales[Amount] by filtering out rows where the Sales ID exists in the Incorrect Payments table.
Sales
Sales ID | Amount |
1 | $50 |
2 | $100 |
3 | $150 |
4 | $100 |
5 | $250 |
6 | $100 |
7 | $150 |
8 | $110 |
9 | $125 |
10 | $108 |
Incorrect Payment
Sales ID | Reason |
4 | Declined Payment |
8 | Invalid Payment Method |
9 | Declined Payment |
10 | Duplicate Payment |
Sales Excluding Failed Payment =
VAR _VariableTable =
CALCULATETABLE (
VALUES ( 'Incorrect Payments'[Sales ID] ),
ALLSELECTED ( 'Incorrect Payments' )
)
RETURN
SUMX (
FILTER ( ALLSELECTED ( Sales ), NOT ( Sales[Sales ID] ) IN _VariableTable ),
Sales[Amount]
)
That's exactly what I needed! Thank you so much!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |