Spyderturbo007
New Member
- Joined
- Mar 11, 2022
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
I've been trying to learn DAX and am stuck at my first calculation. I have two tables that rely on this calculation.
fOrders -> Table that includes all the orders for 2024
dDiscounts -> Table that includes discount codes that can be used for the orders along with a column named Commission Payable. That column determines if the sales person gets paid commission on the discount amount.
In some cases, we give customers a coupon code for 100% off. They then get billed at the end of the month for it's usage. The company still pays commission on that amount, but because of limitations with our software, that code gets lumped into other discount codes that give the customer 10% off. On those codes, we don't pay commission.
My software gives me Order Total, but that total is after the discount is applied. So if I just evaluated things as they are, my sales people wouldn't get paid on some orders that were completely or partially paid later.
I think I want the expression to do a SUMX and when it sees a dDiscounts[Commission Payable], if adds the absolute value of fOrders[Discount Total] to fOrders[Order Total]. If dDiscounts[Commission Payable] = No, then it just returns the fOrders[Order Total] for that row.
That should give me total revenue, some of which is collected at the time of checkout and the balance is collected later via ACH or check.
Hopefully that makes sense. I have this, but I'm not sure if I'm way off base, or if I'm even close. It's giving me an error.
Measure 1:=SUMX(fOrders,IF(RELATED(dDiscounts[Commission Payable])=Yes,fOrders[Order Total]+ABS(fOrders[Discount Total]),fOrders[Order Total]))
fOrders -> Table that includes all the orders for 2024
dDiscounts -> Table that includes discount codes that can be used for the orders along with a column named Commission Payable. That column determines if the sales person gets paid commission on the discount amount.
In some cases, we give customers a coupon code for 100% off. They then get billed at the end of the month for it's usage. The company still pays commission on that amount, but because of limitations with our software, that code gets lumped into other discount codes that give the customer 10% off. On those codes, we don't pay commission.
My software gives me Order Total, but that total is after the discount is applied. So if I just evaluated things as they are, my sales people wouldn't get paid on some orders that were completely or partially paid later.
I think I want the expression to do a SUMX and when it sees a dDiscounts[Commission Payable], if adds the absolute value of fOrders[Discount Total] to fOrders[Order Total]. If dDiscounts[Commission Payable] = No, then it just returns the fOrders[Order Total] for that row.
That should give me total revenue, some of which is collected at the time of checkout and the balance is collected later via ACH or check.
Hopefully that makes sense. I have this, but I'm not sure if I'm way off base, or if I'm even close. It's giving me an error.
Measure 1:=SUMX(fOrders,IF(RELATED(dDiscounts[Commission Payable])=Yes,fOrders[Order Total]+ABS(fOrders[Discount Total]),fOrders[Order Total]))
