Forum Discussion

CS's avatar
CS
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Grand Total Row Count When Using Measures, IF, and FILTER

I want to get the sectors that have increased their hours this month. 

 

=IF( [Total Hours] > [Hours LM],
       IF( 
            HASONEFILTER( 'Table B-7'[Super Sector] ),
            COUNTROWS( 'Table B-7' ),
            ???
        )
)

There are 19 sectors in total. Nine of the sectors have increased their hours. While I can identify the 9 sectors that have increased their hours, I cannot get a correct grand total of 9. I have tried a number of different formulas, but I cannot get the correct total.

 

How can I get the correct row count for the grand total?

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    CS

     

    Give this a shot

     

    ????? =
SUMX (
    FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    HI CS

     

    I think Something along the following lines.

     

    ?????=
SUMX (
    ALLSELECTED ( 'Table B-7'[Super Sector] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)
    • CS's avatar
      CS
      Icon for Advocate I rankAdvocate I
      8 years ago

      Thank you, Zubair_Muhammad.

      I just tried it and I am still getting 19, the total of all the super sector rows. 

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        CS

         

        Give this a shot

         

        ????? =
SUMX (
    FILTER ( ALLSELECTED ( 'Table B-7'[Super Sector] ), [Total Hours] > [Hours LM] ),
    CALCULATE ( COUNTROWS ( 'Table B-7' ) )
)