Hello all,
I have a simple calculation in my table that divides the monthly sales by the number of customers that to give me an average sales per customer.
This works to give the correct monthly value, but the annual total is being incorrectly given as a sum of the monthly totals rather than [SUM(sales)/SUM(cust)]. I have tried using this, but then the monthly total is incorrect. I've also tried variants of CALCULATE and FILTER but cannot get this to work at all - although I am new to Power Pivots.
There are no direct connections between the two tables as they would need to be many to many relationships that aren't supported. So I have created a separate table with the months and am using this to join to both. This may not be the best way to do it, but it works for retrieving the right data.
Any help much appreciated!
I have a simple calculation in my table that divides the monthly sales by the number of customers that to give me an average sales per customer.
Power Query:
=DIVIDE(tbl_financial[Gross revenue],tbl_cust[Sum of cust)This works to give the correct monthly value, but the annual total is being incorrectly given as a sum of the monthly totals rather than [SUM(sales)/SUM(cust)]. I have tried using this, but then the monthly total is incorrect. I've also tried variants of CALCULATE and FILTER but cannot get this to work at all - although I am new to Power Pivots.
There are no direct connections between the two tables as they would need to be many to many relationships that aren't supported. So I have created a separate table with the months and am using this to join to both. This may not be the best way to do it, but it works for retrieving the right data.
Any help much appreciated!
