Group By Filter for Total Amounts

[Anonymous]

So I have a dataset that is full of positive and negative numbers ex:

Bill Number	Charge
1	-10
1	20
2	8
2	-9
2	-3

I want to create a few measures that sum up the count and total for bills that are positive and negative. 

So positive count = 1 Bill with a $10 charge

and negative count would = 1 Bill with -4 Charge 

The problem is not counting bill 1 in the negatives just because it has one negative charge, since its total is positive.   

My measure right now look like this:

Sum of Bills = CALCULATE(SUM(Table[Charge]),GROUPBY(Table,Table[Bill Number]))

Grouped = CALCULATE(DISTINCTCOUNT(Table[Bill Number]),filter(Table,[Sum of Bills] > 0))

This gets me close but I think I am missing one piece to the 2nd query. Any help would be appreciated.

[Anonymous]

This works perfectly, thank you!

[Anonymous · Accepted solution]

Hi @Anonymous 

The following measures are for your reference.

Sum of Bills = CALCULATE(SUM('Table'[Charge]), ALLEXCEPT('Table', 'Table'[Bill Number]))

Grouped = COUNTX(SUMMARIZE(FILTER('Table', [Sum of Bills] > 0), 'Table'[Bill Number]), [Bill Number])

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.