topic Re: Group By Filter for Total Amounts in DAX Commands and Tips

Group By Filter for Total Amounts

Anonymous — Thu, 11 Jul 2024 18:14:29 GMT

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

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 — Fri, 12 Jul 2024 01:51:45 GMT

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.

Anonymous — Fri, 12 Jul 2024 19:29:56 GMT

This works perfectly, thank you!