Power BI summing a column instead of breaking it out by role

Question

I have three tables: Actuals, Estimates, and a Master Projects list.

If I create a bar chart (clustered, stacked, or 100% stacked) by selecting the Role and Hours for the Actuals OR the Estimates, it works fine. It shows the hours for the project broken out by role.

But if I select the Role and Hours for the Actuals AND the Estimates, it breaks.

I need a chart that shows, by project (I'm using a slicer to select), the Estimates by role with the corresponding Actuals. So the PM Estimates should be a bar and right next to it should be the PM Actuals.

But what is happening is I get the PM Estimates for a project and a sum of all (PM, AN, and RN) the Actual hours for that project.

I'm also encountering an issue where I am unable to make a relationship between the Estimates and Actuals. I have to use a Master Project list to act as the go between. If I try to make a relationship between the Actuals and Estimates using the Project as the relationship, it says, "You can't create a relationship between these two columns because one of the columns must have unique values."

Here are the examples of my tables and the results:

Estimates

Actuals

Master Project List

Relationships - I have to include the master project list...I cannot simply make a relationship between the projects column in the Estimates and Actuals

Actuals Chart

Estimates Chart

This is what happens when I attempt to combine the estimates and actuals into a single chart. It will break the hours out by role for the Estimates but will combine all the hours for all roles for the actuals.

Alexis Olson Alexis Olson · Accepted Answer · 2019-08-09T15:30:57

The issue here is that you are using one or the other Role column for your chart x-axis and the tables don't have a way to cross-filter that column from one table to another.

A couple of possible solutions come to mind. The simpler one would be to create a calculated table

Roles = VALUES ( Estimates[Role] )

and set a 1-to-many relationship from that table to Estimates and Actuals in your model. Then you can use the Roles[Role] column on the x-axis and it should filter both tables correctly.

The other approach would be to write a measure that explicitly filters the role. If you are using Estimate[Role] on the x-axis then the Actuals table is the one that needs filtering and you could write

ActualHoursSum =
CALCULATE (
    SUM ( Actuals[ActualHours] ),
    FILTER ( Actuals, Actual[Role] IN VALUES ( Estimates[Role] ) )
)

You may also be able to use the TREATAS function to propagate filters how you want.

Power BI summing a column instead of breaking it out by role

1 Answers