Filters and Measures - Guidance Needed

[jyeager]

Hi there! Back again for more insights from all you smart people. I have a table visual where I would like to show Planned Production, Actual Production, Budgeted Hours for the production activity, and Actual Hours spent on production. Specfically, I created a measure (see DAX below) that takes total budgeted hours and total production quantities and divides the two to get a "Budget per Quantity" for production (in this case its "Budgeted Hours per Pour"). This is where my problem is.... The calcuation in the measure works but when I add it to my table visual, it adds all jobs and the budgeted hours per pour, not just the jobs that produced or were planned to produce on the dates. I added a couple screenshots below to help illustrate. 

Thank you in advance for your thoughts and help!

Here is the DAX for the measure I was referring to.

Budgeted Hours Per Pour = DIVIDE(CALCULATE(SUM(hours_mastertbl[Tot Budgeted Hours]),hours_mastertbl[cost_code_description]="Bed Labor"),CALCULATE(SUM(qtys_mastertbl[budgeted_qty]),qtys_mastertbl[cost_code_description]="Pours"))

Here is a screenshot of the table I am creating. As you can see, job D20007 is correct but the measure added a bunch of extra row with all the other active jobs. I only want to show jobs the produced in the date range I am looking at. 

Here is the data model if it helps! 

[DataZoe · Accepted solution]

@jyeager 

I would give this a try:

Budgeted Hours Per Pour =
IF (
    ISBLANK ( [Actual Hours] ),
    BLANK (),
    SUMX (
        VALUES ( 'jobs_and_products'[Job Number] ),
        DIVIDE (
            CALCULATE (
                SUM ( hours_mastertbl[Tot Budgeted Hours] ),
                hours_mastertbl[cost_code_description] = "Bed Labor"
            ),
            CALCULATE (
                SUM ( qtys_mastertbl[budgeted_qty] ),
                qtys_mastertbl[cost_code_description] = "Pours"
            )
        )
    )
)

I think the issue could also be solved with CROSSFILTER in the measure too, but I usually have to play with that one a bit to get it working the way I want as I don't use it that often. https://docs.microsoft.com/en-us/dax/crossfilter-function I say that because the date filter can't get to the hours_mastertbl and qtys_mastertbl which may be why it's not able to keep the date filter correctly.

[amitchandak]

@jyeager , not very clear try

Budgeted Hours Per Pour = if(isblank([Actual Hour]), blank(),DIVIDE(CALCULATE(SUM(hours_mastertbl[Tot Budgeted Hours]),hours_mastertbl[cost_code_description]="Bed Labor"),CALCULATE(SUM(qtys_mastertbl[budgeted_qty]),qtys_mastertbl[cost_code_description]="Pours")))

[PaulDBrown]

@jyeager 

It looks like you should be using an iteration function (SUMX) to iterate the calculations row by row (by date?)

[Greg_Deckler]

@jyeager - I don't see anything obvious in the measure like an ALL or ALLEXCEPT. Could be a missing relationship or direction of a relationship, I'll have to look closer. Any chance you can share PBIX?

Wait, do you just need to add some slicers to the page? How are you selecting your date range?