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

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