Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Exclude particular row in total

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 t...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Zubair_Muhammad
Community Champion
7 years ago

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