Forum Discussion

AndyTrezise's avatar
AndyTrezise
Icon for Advocate IV rankAdvocate IV
9 years ago
Solved

Count based on measure

I Have a table that consists of transactions as follows:   txn date          type     ref          budget    actual    variance 01/10/2016     inv       1            25.00       30.00     5.00 02...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    AndyTrezise

     

    In this scenario, if you want to calculate the overuns for each ref, you should filter the table based on Variance in your COUNTROWS calculation. If you want to count the ref which overuns above zero, you need to distinct count ref column.

     

     

    Overruns = IF(CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0))=BLANK(),0,CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0)))

     

     

     

     

    Count Ref above zero = CALCULATE(DISTINCT(Table8[ref]),FILTER(Table8,[Overruns]>0))

     

     

    Regards,

     

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

AndyTrezise

 

In this scenario, if you want to calculate the overuns for each ref, you should filter the table based on Variance in your COUNTROWS calculation. If you want to count the ref which overuns above zero, you need to distinct count ref column.

 

 

Overruns = IF(CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0))=BLANK(),0,CALCULATE(COUNTROWS(),FILTER(Table8,[Total Variance]>0)))

 

 

 

 

Count Ref above zero = CALCULATE(DISTINCT(Table8[ref]),FILTER(Table8,[Overruns]>0))

 

 

Regards,

 

AndyTrezise's avatar
AndyTrezise
Icon for Advocate IV rankAdvocate IV
9 years ago

Thanks for the feedback

 

Managed to sort the problem with your advice

 

Cheers