Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count based on 2 tables

Hi All,

 

Req: I have 2 tables ,I wanted to count fruits which has 1 or more fruits then count as 1 , if no fruits display as 0 and also  grand total also required

 

please don’t use any visual level filters likne fruit = blank because I have some other calculations based on this so looking for formula based on 2 tables

 

Please find attached data set:

 

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    HI, Anonymous 

    You could use this formula to get it.

    fruit count = var _table=SUMMARIZE(Table2,Table2[ID],"_value",IF( CALCULATE(COUNTA(Table2[Fruit]),FILTER(Table2,Table2[Fruit]<>BLANK()))>=1,1,0)) return
SUMX(_table,[_value])
    CountID = CALCULATE(COUNTA(Table1[ID]))

    Result:

    here is pbix file, please try it.

     

    Best Regards,

    Lin

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    HI, Anonymous 

    You could use this formula to get it.

    fruit count = var _table=SUMMARIZE(Table2,Table2[ID],"_value",IF( CALCULATE(COUNTA(Table2[Fruit]),FILTER(Table2,Table2[Fruit]<>BLANK()))>=1,1,0)) return
SUMX(_table,[_value])
    CountID = CALCULATE(COUNTA(Table1[ID]))

    Result:

    here is pbix file, please try it.

     

    Best Regards,

    Lin