Power BI Summation Error

[Anonymous]

Hi,

I have a dataset that shows the sum of amount invoiced per month in 2019.  I have filtered out all negative amounts.

There is no data for Jan 2019 and I assumed that this was because Jan 2019 had no invoices with positive amounts issues. However, when I click on the don't summarize option for amount, the individuals amounts (all positive) appear. There is positive amount data for Jan 2019 in the backend as well.

Why is this happening?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

How did you filter out all negative amounts? Using the filter pane? If so, filtering may happen after the aggregation sum. 

In your scenario, I think it would be better to use a measure to calculate the sum of positive amounts per month. E.g.

total = CALCULATE ( SUM ( 'Table'[amount] ), 'Table'[amount] >= 0 )

or

total = SUMX ( FILTER ( 'Table', 'Table'[amount] >= 0 ), 'Table'[amount] )

You can put above measure into the table visual to replace Amount(USD) column. In the measure, filtering happens earlier than aggregation sum.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[nvprasad]

Hi atinakarim,

Probably the sum of negative values is higher than positive values. Hence. when you select don't summarize no aggregation is happening and you are able to see all positive numbers.

Appreciate the Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad