Count filtered by measure and circular refrence

[Pmorg73]

Hi everyone

I am working on a little data dive. Working on historic data from projects I have worked on designing houses. I have a table of job information and this contains the original budget and floor area. I calculate the budget per m^2. I aslo then calculate the sum of the billable time spent on the job. divide that by the area and get Billable per m^2

All going good so far. I then built "Size slicer" table to enable me to categorize the jobs based on the floor area.

To then work out the average of the "Billable per m" I needed a count. This forum helped me get to the solution below. Many thanks btw

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Count =

CALCULATE([count basic],

FILTER(ADDCOLUMNS(VALUES('house drill'[Job No.]),"ABCD",

CALCULATE([Area basic],

CALCULATETABLE(VALUES('house drill'[Job No.])))),

COUNTROWS(FILTER('Size slicer',[abcd]>'Size slicer'[Lower]&&[ABCD]<='Size slicer'[Upper]))))

xxxxxxxxxxxxxxxxxxxxxxxxxx

Now in the image below I have a talbe of results, but I would like to filter out the outliers where :Billed to Billable" is >25

Basically these will have been jobs that went badly and I am using the tool to price future jobs of the same types.

If I merely filter the table (easy) then it will not affect the count. But the measure relies on the count so my efforts so far have resulted in circular expressions. My ultimate result is the Average card. Ave = divide(billable per m, count)

Any suggestions on how to filter the count by this calulated measure which relies on table? you will see I already have a virtual table in my count measure, but its new to me and I dont know how to multple filter it

[Pmorg73]

https://batchelarmcdougall-my.sharepoint.com/:u:/g/personal/phil_bmconsult_co_nz/ESNsQe376YRHko3vlmU...

[m3tr01d · Accepted solution]

Hi @Pmorg73 ,

Some things that will help you :
1) The attributes in house drill that are duplicated shouldn't belong in the house drill table.
Ex: Floor Area. Floor Area is probably an attribute of Job No and probably belongs in the Job No number
Other attributes that should probably be moved : Budget, Job State, Designer Complexity, Architect ...

2) Your biggest issue is how you handle your Size slicer. You need to change the way you use it. It would be

so much easier for you to create a calculated column in the Job No table that have the size.

Right now, what I ended up doing is adding a calculated column in the house drill.

Size = 
CALCULATE(
    MIN( 'Size slicer'[Size] ),
    FILTER(
        'Size slicer',
        'house drill'[Floor Area] >= 'Size slicer'[Lower] &&
        'house drill'[Floor Area] < 'Size slicer'[Upper]
    )
)

 3) You probably don't need the two calculated column for Budged per m and Billable per m.

I'll send you the pbix I modified in PM

[Pmorg73]

thanks so much for you help. Will download and digest the changes 🙂

[m3tr01d]

hello @Pmorg73 ,
basically you would like to eliminate the Job No where Billed to billable >= 25 in the calculation of the Count card and Average billable per m card?

Also, are you sure that the Average billable per m card compute the correct result? It seems to me that the code 
DIVIDE( [billable per m], [count] ) won't give you the result you're expecting but maybe I'm wrong.

[Pmorg73]

"basically you would like to eliminate the Job No where Billed to billable >= 25 in the calculation of the Count card and Average billable per m card?" - yes thats right. And the count is dependant upon the number of jobs that result from the slicer. A simple fix is to transform data and simply exclude the offending jobs, but I was trying to make it dynamic if I can.

"Also, are you sure that the Average billable per m card compute the correct result? It seems to me that the code
DIVIDE( [billable per m], [count] ) won't give you the result you're expecting but maybe I'm wrong."

It does seem to be doing what I aiming for example below where I used the slicer. I sliced for a list of 9 jobs. I turned totals on. total Billable per m = $755.81 and the count is 9. And at this amount of jobs thats easy to check. the average therfore is Divide(755.81, 9) = $83.98. So this seems correct to me. the trick now is the extra filter. I will upload my pbx below

[Vera_33]

Hi @Pmorg73 

What is the calculation of your measures [count basic], [Area basic]? And Billed to Billable is >25, this is another measure? What do you mean by relying on the Count? Re write your Count a little bit based on my understanding

Count 1 =
VAR T1=ADDCOLUMNS (
            VALUES('house drill'[Job No.]),
            "ABCD", [Area basic])
RETURN
COUNTROWS(FILTER(T1,[ABCD]>MIN('Size slicer'[Lower])
                    && [ABCD] <=MAX( 'Size slicer'[Upper]))
)

[Pmorg73]

count basic = distinctCOUNT('house drill'[Job Summary])

Area basic = MIN('house drill'[Floor Area])

I used Min just to return a single value as the data contains multiple rows per job, all with the same area value returned from our database.