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 ...
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.

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.