Show totals if greater than zero

[abloor]

Hi, I need some help please and haven't been able to find anything similiar online to help me.

I need to show the total number of clients that spent with us, and the average spend.  BUT I only want them included if they spent greater than $0.  (some had freebies booked in at $0, some did cancellations so appear as -$xxx.).  All the $0 and -$xxx are being included currently in my count and average

I have tried using a Table visualization and dragging in the field of client name, and ticking 'Distinct Count' on.  That does give me the total number of clients, but it includes clients that got freebies and those that cancelled. How do I exclude them?

I also want to see the average spend of anyone greater than zero.  I created the below meausre, but it's taking into account the $0 and -$xxx.

I've tried using the filter pane on my client name, revenue measure and average spend measure to see if I can set it to show only spends of greater than 1, but it won't even let me click in the filter boxes.  I thought it was a bug and did a close and open, but it didn't work, so I assume what I'm trying to do might not be possible.

Any ideas?

Thanks very much.

Average Spend =

AVERAGEX(

    KEEPFILTERS(VALUES('Data'[ClientName])),

    CALCULATE([REVENUE])

)

[Nathaniel_C]

Hi @abloor ,

countx = 
Countx('Table',IF('Table'[Column1]>0,'Table'[Column1]))

averagex = 
AVERAGEX('Table',IF('Table'[Column1]>0,'Table'[Column1]))

Try this.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 

[abloor]

Thanks for your time @Nathaniel_C , much appreciated.

It doesn't seem to be working, and I think it's because my clients have several rows of data each e.g. as below (where col 2 is client name).  Do you think this is a factor that needs considering? Cheers.

Column 1	Column 2
a	59
a	10
b	69
b	50
b	42
b	45
b	60
c	10
c	-20
d	0

[Nathaniel_C]

Will check which part is not working?

[abloor]

Thank you @Nathaniel_C . Sorry I should have been more specific.

In my above table, it's counting column 1 as '10' clients.  I want to see 3 as the result (a / b / c . We won't count D as it's $0).

And it's averaging the $ as $32.5 (total of col 1 divided by 10 rows.)

But I would like to see an average of the total spend of each client (e.g. by totalling each client's spend that is greater than $0, then dividing by the number of clients. I get $86.25 with the example data.) (Or you could get the total of all money rows that are greater than $0, then divide by the 4 clients.)

Hopefully that is clear. Thanks 

[Nathaniel_C]

First part . I think are column names are reversed.  This gives four. However, do you want to count if the sum of a client is zero or less? Then there should be 2. What do you think?

CountX test = 
var _calc = CALCULATE(DISTINCTCOUNT('test'[Column1]))



return _calc

[Nathaniel_C]