cancel
Showing results for
Did you mean:
Christian_DC Frequent Visitor

## Transpose table with weighted averages

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.

Highlighted
Christian_DC Frequent Visitor

## Re: Transpose table with weighted averages

To add to this, I appreciate that I can get the aggregated, customer level data in the report by adding a table and the weighted averages calculated in the new measures added.  However, I then need to use the customer-level data for further manipulations, and therefore it is insufficient as a report output, I need to export it back as an additional query if possible.

🙂

Thanks again everybody.  Viva Power BI!

Announcements   