Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI Summation Error

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?

 

 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.