Forum Discussion
Group By Filter for Total Amounts
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:
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.