Count of Customers according to the average billed amount

[Andrew_na_lang]

Hi Guys,

I am currently stuck with the COUNT function on power bi. I need help!

 I need to count the number of customer name with an average billed amount of :

< 45

>45 and <=55

>55 and <=100

>100 and <= 200

>200

Total billed amount, count of orders, and average billed amount are measures I created. 

please help!!! Thank you.

[Andrew_na_lang]

Yes, the count should be updated by the slicers. 

[v-easonf-msft]

Hi, @Andrew_na_lang 

What related fields do you apply to slicers?
Can you share relevant screenshots?

Best Regards,
Community Support Team _ Eason

[Andrew_na_lang]

I use this 

Avg billed less than 45 = COUNTROWS( FILTER( Customer, [Average billed amount] <= 45 ) )

but the figures that showed up didn't add up. 

I need to get a distinct number of customers according to their average billed amount. pls

[johnt75 · Accepted solution]

It depends on whether you need the calculation for average billed amount to be affected by slicers and filters, e.g. date range. If you don't need that then you could create a calculated column on the customer table like

Average billed group =
var billedAmount = [Average billed amount]
return SWITCH ( TRUE(),
    billedAmount <= 45, "<= 45",
    billedAmount > 45 && billedAmount <= 55, "> 45 and <= 55",
    billedAmount > 55 && billedAmount <= 100, "> 55 and <= 100",
    billedAmount > 100 && billedAmount <= 200, "> 100 and <= 200",
    "> 200"
)

but if you need to take into account slicers and filters then you would need to create a separate measure for each range, something like

Avg billed less than 45 = COUNTROWS( FILTER( Customer, [Average billed amount] <= 45 ) )