Greetings all,
I am having a real challenge trying to replicate a procedure I used to do within pivot tables in Excel.
The data I am looking at are millions of customer invoices, and I'm interested in understanding various timings on a weighted average basis for each customer. For example, each row will include the an invoice with details such as order date, invoice date, due date, payment date, etc. so I would calculate the number of days between each date and then aggregate these data to show the weighted average timings for each customer (and each customer may have thousands of invoices during the year).
I used to do this in Excel by multiplying each # of days by the invoice value, then reversing this out within a pivot of the whole data set. For example, the raw data might be 'Customer', 'Value' and 'Terms', as follows:
| Customer | Value | Terms | x wat |
| A | 1000 | 50 | 50000 |
| A | 10000 | 20 | 200000 |
| A | 50 | 50 | 2500 |
| B | 5000 | 14 | 70000 |
| B | 100 | 12 | 1200 |
| B | 20000 | 40 | 800000 |
Each customer has three invoices with differing terms and different values. I have added 'x wat', which is 'Value' multiplied by 'Terms'. I pivot the whole data set, go to 'Fields, Items & Sets', then add 'Weighted Average Terms' as = 'x wat'/Value
I add more formulae for each weighted average calculation I need, resulting in an aggregated list of customers with their weighted average timings calculated. In this simple example, it would look like this:
| Row Labels | Sum of Value | Sum of WA terms |
| A | £ 11,050 | 22.9 |
| B | £ 25,100 | 34.7 |
| Grand Total | £ 36,150 | 31.1 |
So, I can't seem to find a way to do this in Power BI!
I need to take a large number of invoices (sitting in one Query), add measures for the weighted averages (which I can do, no problem, e.g. WA payment terms = SUMX('By customer',[Sum of Value]*[Sum of WA terms]) / SUM([Sum of Value])) but then to create another query with the data aggregated at the customer level, with numbers that represent total value of invoices by customer, but then weighted average payment terms, weighted average days to pay, etc., just as I used to do in Excel.
If anybody is able to help, it would be very, very much appreicated!
Many thanks all and have a great day.
