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 the 'Year1' cause there's no value anymore.

 

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

 

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] ) )
    )
)

     

     

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    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
        Icon for Community Champion rankCommunity 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