The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
Hi all
I have a table with transactions in it as well as well as rate per transaction. I would like to write a dax formula that finds the rate for the month and for the customer, and multiple it together to get to revenue. I could separate the transactions and rates into 2 different tables, but wanted to try something different. Example: ACME01, 1/1/21 = 100 * 10 = 1000. ACME01, 2/1/21 = 110 * 20 = 2200, etc. Is there any easy way to do this or should I just separate the rates and transactions into 2 tables?
GP Vendor ID | Measure | Value | Month |
ACME01 | transactions | 100 | 1/1/2021 |
ACME01 | rate | 10 | 1/1/2021 |
ACME01 | transactions | 110 | 2/1/2021 |
ACME01 | rate | 20 | 2/1/2021 |
ABCE01 | transactions | 115 | 3/1/2021 |
ABCE01 | rate | 30 | 3/1/2021 |
I tried that and I got some really big numbers. I gave you a small sample of a larger table. Would it be different if there were multiple GP Vendor IDs in a month? Here is an example:
GP Vendor ID | Store | Measure | Value | Month |
ACME01 | 1 | transactions | 100 | 1/1/2021 |
ACME01 | 1 | rate | 10 | 1/1/2021 |
ACME01 | 2 | transactions | 110 | 1/1/2021 |
ACME01 | 2 | rate | 20 | 1/1/2021 |
ACME01 | 3 | transactions | 115 | 1/1/2021 |
ACME01 | 3 | rate | 30 | 1/1/2021 |
User | Count |
---|---|
15 | |
13 | |
9 | |
6 | |
6 |
User | Count |
---|---|
28 | |
18 | |
13 | |
9 | |
5 |