SUMIF Excel Formula performed in Power BI between two tables using column data as the criteria

[arickard]

I have two tables within Power BI. The first table (tblWIP) contains a JobID and hour units associated with that JobID with each row reflecting every time new time input is loaded for a job. The second table (tblJob) contains a summary of those same JobIDs. At the end of every year a new JobID is assigned to the same job (i.e AU19 JobID would be 1504136, but then for the same client AU20 JobID would be 1732174), so the tblJob also contains a column that reflects the prior year JobID associated with the current year JobID. I'm trying to create a table in Power BI that would show the current year JobID budgeted time vs. the prior year actual time using the two tables of data.  I could accomplish it in Excel by creating a SUMIF column that sums the units by job from tblWIP using the prior year JobID in the tblJob, but can't figure out how to accomplish this same result in Power BI with a measure.

The first screenshot is of the tblJob in Excel with the PY Actual SUMIF column included. I'm using the PriorYearJobID as the criteria to sum from the JobID on the tblWIP sheet.  So the 243 PY Actual is now associated with JobID 1732174.  The second screenshot shows the date currently in Power BI.  I want to get that 243 Approved Hours associated with JobID 1504136 moved up one line to a PY Actual column for JobID 1732174 based on the relationship between the two tables.

[v-yiruan-msft]

Hi @arickard,

Please make sure the relationship has been created between table tblJob and tblWIP with field JobID first, then you can create a measure with the below formula to get the value of PY Actual:

PY Actual =
CALCULATE (
    MAX ( 'tblWIP'[Approved Hours] ),
    FILTER ( ALL ( 'tblWIP' ), 'tblWIP'[JobID] = MAX ( 'tblJob'[PriorYearJobID] ) )
)

If the above formula is not applicable for your scenario, please provide more details to describe your needs. Thank you.

Best Regards

Rena

[arickard]

Rena,

Thanks for the response. I think what you provided almost gets me there. One clarification about the data is that budget and approved hours data is not in a summazation form within the data, but itstead there are a large number of rows within the tblWIP reflecting units associated with the JobID/PriorYearJobID that need to be summed.  How would I adjust your measure to account for having to sum the approved hour/PY actual units?  I already have an Approved Hours measure currently,

Approved Hours = CALCULATE(SUM(tblWIP[Units]),tblWIP[ChargeTypeID]=24), that sums the units based on a charge type, which is needed to identify the type of units needed to sum for actual hours.

Thanks again!

Aaron

[v-yiruan-msft]

Hi @arickard ,

Then could you please provide me your actual table data(include Units, ChargeTypeID and other necessary fields )? Later I will update the formula of measure and share it with you. Thank you.

Best Regards

Rena

[arickard]

Hi @v-yiruan-msft,

Below is a link to the  summary file of the data I am working with.  First sheet (tblJob) includes the JobID and PriorJobID columns.  Second sheet (tblWIP) includes the JobID, ChargeTypeID and Units.  So I am needing what you already demonstrated, but the tblWIP needs to sum the Units by JobID only for ChargeTypeID "24" and associate it with the PriorJobID. What you initially demonstrated was exactly what I was looking for with the exception of the summing aspect that I hadn't explained originally.

https://drive.google.com/open?id=14H1WcFPB-km5Yq-rLFzx4-uCt2yY6Jfb

Thank you for your help!

[v-yiruan-msft · Accepted solution]

Hi @arickard ,

You can update the formula as below:

PY Actual = 
CALCULATE (
   SUM('tblWIP'[Units]),
    FILTER ( ALL ( 'tblWIP' ), 'tblWIP'[JobID] = MAX ( 'tblJob'[PriorJobID] )&&'tblWIP'[ChargeTypeID]=24 )
)

Best Regards

Rena