cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Count based on measure

@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 3
Highlighted
Member

## Re: Count based on measure

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

Moderator

## Re: Count based on measure

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

Regular Visitor

## Re: Count based on measure

Thanks for the feedback