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' ) )
)
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.