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( '...
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' ) )
)
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' ) )
)
    • CS's avatar
      CS
      Icon for Advocate I rankAdvocate I
      8 years ago

      That worked, thank you. 

       

      I thought measures could not be used in FILTER. I tried using measures in FILTER to no avail. When I read up on it, I found something that said measures cannot be used in FILTER. But look at this, they can. 

       

      Thank you again.