Exclude particular row in total

[Anonymous]

Hi,

I am trying to show a table where in a Matrix, the total excludes Year1, where my Total would be 4400 instead of 4900 and yet Year1 data is still there.

I used this measure, but it removed the 'Year1' cause there's no value anymore.

Total = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Table (Year)] <>"Year1"))

 

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

Try this measure

Spoiler

Measure =
IF (
    HASONEFILTER ( 'Table'[Year] ),
    SUM ( 'Table'[Amount] ),
    SUMX (
        FILTER ( VALUES ( Table[Year] ), 'Table'[Year] <> "Year1" ),
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )
)

[Anonymous]

@Zubair_Muhammad  It worked!! Can you explain a little for me please?

I am trying to piece together how each formula works. Individually, I kinda understand, but putting them together seems a bit confusing... Sorry..

[Zubair_Muhammad]

Sure @Anonymous 

The formulas like HASONEFILTER,HASONEVALUE,ISFILTERED are useful in detecting totals and specifying different calculations for them.
The total row contains all the years you have selected

Here is a very good article

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/.

So in the if clause we apply the normal sum(sales) calculation if the YEAR row contains a single year.
In the totalrow we sum the sales of all the years (excluding an year) using an interator function SUMX