cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyTrezise Regular Visitor
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 v-sihou-msft
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.

 

1.PNG

 

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

 

 

2.PNG

 

 

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

 

3.PNG

 

Regards,

 

3 REPLIES 3
Highlighted
nirrobi Member
Member

Re: Count based on measure

Hi,

 

Hope I understood your query correctly,

 

Let's begin from the end :-)

finaltable.JPG

 

 

 

 

 

 

 

 

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 v-sihou-msft
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.

 

1.PNG

 

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

 

 

2.PNG

 

 

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

 

3.PNG

 

Regards,

 

AndyTrezise Regular Visitor
Regular Visitor

Re: Count based on measure

Thanks for the feedback

 

Managed to sort the problem with your advice

 

Cheers