Forum Discussion

Pmorg73's avatar
Pmorg73
Icon for Post Patron rankPost Patron
5 years ago
Solved

Count filtered by measure and circular refrence

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
 

 

 

 

Need Help
  • m3tr01d's avatar
    m3tr01d
    5 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Pmorg73
      Icon for Post Patron rankPost Patron
      5 years ago
      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.
  • m3tr01d's avatar
    m3tr01d
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    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's avatar
      Pmorg73
      Icon for Post Patron rankPost Patron
      5 years ago

      "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

       

    • m3tr01d's avatar
      m3tr01d
      Icon for Continued Contributor rankContinued Contributor
      5 years ago

      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's avatar
        Pmorg73
        Icon for Post Patron rankPost Patron
        5 years ago

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