Forum Discussion

hhoward8's avatar
hhoward8
Frequent Visitor
8 years ago

weighted average

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    $               ...
Need Help
v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

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.

hhoward8's avatar
hhoward8
Frequent Visitor
8 years ago

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,