Count based on measure

[AndyTrezise]

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.

[v-sihou-msft · Accepted solution]

@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]

Thanks for the feedback

Managed to sort the problem with your advice

Cheers

[nirrobi]

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