Average with a check against total non-zero and a distinct count

[FPP]

I have the following table:

Note: I have a tran date that I did not inlcude below.  It is related to a calendar table via tran date.

Cls Date	Key 1	Key 2	Cat	Status	Amount
	A1	B1	A	open	2
Jan 1,2020	A1	B2	A	closed	10
Jan 1,2020	A1	B3	A	closed	20
Jan 1,2020	A2	C1	A	closed	5
Jan 3,2020	A2	C2	A	closed	-5
Jan 1,2020	A3	D1	B	closed	0
Jan 2,2020	A4	E1	A	closed	50
Jan 2,2020	A5	E2	A	closed	-50
	A5	E3	B	open	30
Jan 1,2020	A6	F1	A	closed	40
Jan 2,2020	A6	F2	B	closed	-40

I need to crate an average that does this:

For each cls dte month do the following

1. Add up the amount and count unique Key 1
2. Only include Key 1 if the sum of amount is not 0 for Key 1 inside the month

Cls Date Month	Count of Distinct Key 1 where Total Amount <> 0	Sum of Amount	Note
Jan, 2020	1	10+20=30	Counted A1 Not counted A2 since 5-5=0, A3 since  0=0, A4, A5 since 50-50=0, A6 since 40-40=0

Average 30/1=30
Note 2 and 30 are not counted or added since they have no close date.

When doing it by Cat

Cls Date Month	Cat	Count of Distinct Key 1 where Total Amount <> 0	Sum of Amount	Note
Jan, 2020	A	2	10+20+40=70	Counted A1, A6 since it is 40 for Cat A Not counted A2 since 5-5=0, A4, A5 since 50-50= 0
Jan, 2020	B	1	-40	Counted  A6 since it is -40 for Cat B Not counted A3 since  0=0

Average Cat A  70/2=35
Average Cat B  -40/1=-40

Since the user can pick Cat via a filter visual on the screen.

How would I do this?

Fernando

[amitchandak · Accepted solution]

@FPP  Try a meausre like

divide( sum(Table[Amount]) 

     , Countx(filter(summarize(Table, Table[Key 1] , "_1", sum(Table[Amount])), [_1] > 0 ), [Key 1])

)