How to count customers with revenue less than £5k?

[xjianguk]

Hi,

This might be an easy question but I'm struggling to find a solustion:

Basically I'm trying to calculate the distinct count of the customers who have revenue of less than £5000 (at customer level). 

An example of the data is as below. So there should be 3 customers that have revenue of below £5k.

My original formula is shown below this but it counts all the below 5k transaction at 'Job Number' level, not customer level. i.e. it returns with a distinct count of 5 instead of 3.

Count of Customer < £5k =

CALCULATE(

DISTINCTCOUNT('Table'[Customer]),

FILTER('Table','Table'[Revenue]<5000))

Many thanks for your help!

[bcdobbs · Accepted solution]

Try this:

Count of Customer < £5k = 
VAR CustomerRevenue =
FILTER ( 
    SUMMARIZECOLUMNS (
        Revenue[Customer],
        "Revenue[Total Revenue]", CALCULATE ( SUM (Revenue[Revenue] ) )
    ),
    Revenue[Total Revenue] < 5000
)

RETURN COUNTROWS ( CustomerRevenue )

[raimon · Accepted solution]

Hi @xjianguk,

Please use following measure - 

[Replace 'Customer Table' with the name of your table]

Measure = CALCULATE(DISTINCTCOUNT('Customer Table'[Customer]),filter(SUMMARIZE('Customer Table','Customer Table'[Customer],"total",SUM('Customer Table'[Revenue])),[total]<5000))

[xjianguk]

Many thanks for your help! 

[mh2587]

COUNTAX(Table(customer),revenue>5k)

[bcdobbs · Accepted solution]

Try this:

Count of Customer < £5k = 
VAR CustomerRevenue =
FILTER ( 
    SUMMARIZECOLUMNS (
        Revenue[Customer],
        "Revenue[Total Revenue]", CALCULATE ( SUM (Revenue[Revenue] ) )
    ),
    Revenue[Total Revenue] < 5000
)

RETURN COUNTROWS ( CustomerRevenue )

[xjianguk]

Many thanks for your help!