Forum Discussion

AndyTrezise's avatar
AndyTrezise
Advocate 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/10/2016     inv       2            20.00       18.00    -2.00

03/10/2016     inv       1            22.00       22.00     0.00

04/10/2016     inv       1            27.00       31.00     4.00

05/10/2016     inv       2            20.00       19.00    -1.00

 

The 'variance' is a measure I have created to subtract budget from actual.

 

I wish to show a summary of this in a table as follows:

 

ref    budget     actual     variance     overruns

1      74.00        83.00      9.00            2

2      40.00        37.00     -3.00           0

 

I can achieve most of this however I don't appear to be able to count the overruns.

 

I thought it would be something like CALCULATE((COUNTROWS('MYTABLE'),FILTER('MYTABLE',[OVERRUNS]>0)) but this gives the wrong results and appears to count all records.

 

Any help would be much appreciated.

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,

     

3 Replies

  • nirrobi's avatar
    nirrobi
    Helper V
    9 years ago

    Hi,

     

    Hope I understood your query correctly,

     

    Let's begin from the end :-)

     

     

     

     

     

     

     

     

    I accomplished it with the following steps:

    1. Create Measure for Vat
      1. Var = SUM(MyTable[actual])-SUM(MyTable[budget])
    2. Create Measure for Overruns
      1. Overruns =
           CALCULATE(
              COUNTROWS(MyTable),
           FILTER(
              MyTable,
              MyTable[Var]>0))

     

    AndyTrezise

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft 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
      Advocate IV
      9 years ago

      Thanks for the feedback

       

      Managed to sort the problem with your advice

       

      Cheers