weighted average

[hhoward8]

Hi everyone!

I'm trying to create a type of average. The example in excel is as shown:

Payment Receiving
Day Buckets	Days	Amount	Days*Amount
1-30	30		$                               -
31-60	60	$     8,265.73	$              495,943.80
61-90	90	$   17,199.23	$           1,547,930.70
91-120	120	$         959.74	$              115,168.80
121-150	150	$     1,324.25	$              198,637.50
151-180	180	$     5,953.75	$           1,071,675.00
181-210	210	$     1,175.15	$              246,781.50
211-240	240	$         812.06	$              194,894.40
241-270	270	$         218.32	$                58,946.40
271-300	300	$         834.27	$              250,281.00
301-330	330	$         423.02	$              139,596.60
331-360	360	$           42.96	$                15,465.60
361-390	390		$                               -
481-510	510		$                               -
601-630	630		$                               -
721-750	750		$                               -
	TOTAL	$   37,208.48	$           4,335,321.30
	AVERAGE	116.51

I'm trying to get to the AVERAGE value that is created at the bottom. The issue is, I'm trying to show this Average for of our Top N (10) payers in a single table, alongside their total payments, and the weight of their total payments for the ALL payers (not just top N). i.e.:

Payer	Avg Days to Pay	Total Payments	Weight
Example Co.	116.51	$           37,208.48	0.11%
Big Bank	64.8	$           46,928.54	2.93%
Imaginary Store	109.7	$           40,663.51	0.13%

The charges are in buckets per a date difference calculated between when a good is sold and payment is received. So the Charges for that "bucket" is the total charges for that payer in that timeframe. Does anyone have any guidance on how best to do this? I'm connected to SSMS and the table includes a large amount of data (>2M rows).

"Payments" is measure in the table that this is all calulcated off of. Right now Date Diff is calculated as:

DateDiff = DATEDIFF([Date Sold],[Date Paid],DAY) and returns a whole number. 

Buckets is calculated as:

Buckets = IF(Table[DateDiff] < 31, 30, IF(
Table[DateDiff] < 61 && Table[DateDiff] > 30, 60, IF(
Table[DateDiff] < 91 && Table[DateDiff] > 60, 90, IF(
Table[DateDiff] < 121 && Table[DateDiff] > 90, 120, IF(
Table[DateDiff] < 151 && Table[DateDiff] > 120, 150, IF(
Table[DateDiff] < 181 && Table[DateDiff] > 150, 180, IF (
Table[DateDiff] < 211 && Table[DateDiff] > 180, 210, IF(
Table[DateDiff] < 241 && Table[DateDiff] > 210, 240, IF(
Table[DateDiff] < 271 && Table[DateDiff] > 240, 270, IF(
Table[DateDiff] < 301 && Table[DateDiff] > 270, 300, IF(
Table[DateDiff] < 331 && Table[DateDiff] > 300, 330, IF(
Table[DateDiff] < 361 && Table[DateDiff] > 330, 360))))))))))))

[v-xjiin-msft]

Hi @hhoward8,

=> I'm trying to show this Average for of our Top N (10) payers in a single table.

How could we know which payers are the top N? Does there exist a sequence column?

Generally to get top n rows in Power BI, we can use RANKX() function to make an order. Let's make a sample based on your sample data to get top 10 rows total amount and average amount.

You can use RANKX() function to make an order.

Rank =
RANKX ( Table1, Table1[Days],, ASC, DENSE )

Then use this Rank to get top 10 total amount and average amount.

Top 10 total amount =
CALCULATE ( SUM ( Table1[Amount] ), Table1[Rank] <= 10 )


Top 10 days =
CALCULATE ( MAX ( 'Table1'[Days] ), Table1[Rank] = 10 )


Top 10 average =
[Top 10 total amount] / [Top 10 days]

Thanks,
Xi Jin.

[v-xjiin-msft]

Hi @hhoward8,

Yes. TOPN() function is better than RANKX() in your scenario.

=> The issue is, I'm trying to show this Average for of our Top N (10) payers in a single table, alongside their total payments, and the weight of their total payments for the ALL payers (not just top N).

So you want to calculate the average just for the top N payers. However the weight should be based on all payers. Right? 

Could you please share us a sample pbix file with multiple payers source data if possible? Since your current sample data is only for one payer and we don't know your source table structure. It is hard for us to provide a proper solution.

Thanks,
Xi Jin.

[hhoward8]

Hi @v-xjiin-msft,

The Top 10 are currently being shown by using the Top N filter in the built in function. I filter the Payer Name by Payments (i.e. whoever pays the most is shown), so there is no need for a rank function unless this filter method does not work properly (right now it does, though). 

Thanks,