Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Remove row value from total calculation

Hi Team, 

 

I have a table in Power BI visual with the rows and totals. Now while calculating totals, I need to remove one row value but include the others. 

 For now the total in excel is sum of row 2-row8 and it exclude the value in row 9. I need the same thing in PBI. I need the datat to show for that project but in totals, it should not calculate that amount.

 

 

Need Help

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Anonymous ,

     

    The robustness and reliability of Power BI and Power Pivot, compared to Excel, lie in their ability to perform calculations on entire columns rather than individual cells. As a result, it’s not possible to manually exclude specific rows without applying some logical criteria. My recommendation in this case is to introduce a field in your data table to flag the rows that should be excluded from the total. You can then use the 'calculate' function to exclude those flagged rows from the total calculation.

     

    Best regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    Please try this:

    Here I create a set of sample:

    Table:

    Open the power query and add a Index Column in the Add Column pane:

    Apply and Close.

    Then add a measure:

    SumValue =
IF (
    HASONEVALUE ( 'Table'[ID] ),             //Determine whether it is a Totar line
    SUM ( 'Table'[Values] ),                 //Returns a value for a non-Total line
    SUMX (
        FILTER ( 'Table', 'Table'[Index] >= 2 && 'Table'[Index] <= 8 ),
        'Table'[Values]
    )                                        //The sum of lines 2-8 is returned in the total line
)

    The result is as follow:

    HASONEVALUE function (DAX) - DAX | Microsoft Learn

    FILTER function (DAX) - DAX | Microsoft Learn

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.