Remove row value from total calculation

[mohiniverma2704]

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.

[Anonymous]

Hi @mohiniverma2704 

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.

[DataNinja777]

Hi @mohiniverma2704 ,

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,