cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Christian_DC Frequent Visitor
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:

 

CustomerValueTerms x wat
A10005050000
A1000020200000
A50502500
B50001470000
B100121200
B2000040800000

 

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 LabelsSum of ValueSum of WA terms
A £          11,05022.9
B £          25,10034.7
Grand Total £          36,15031.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.

1 REPLY 1
Highlighted
Christian_DC Frequent Visitor
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!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,649)