PowerBI measure is giving an unexpected value

Question

I'm setting up a report in PowerBI that will give insight into the amount of work that is planned per department. I know the amount of products to be manufactured for specific projects and the time this takes per department in minutes. I've created a measure to calculate the total amount of time planned per department for a specific period, but it is giving an unexpected total that I don't understand.

The datamodel consists of four tables: a table that contains unique product ids, a table that contains information on running projects (including the amount of products to be manufactured by a specific date), a table that contains the manufacturing routing which shows how much time is required to manufacture a specific product per department, a calendar table that contains all dates for the next 10 years

Relationships between the tables currently all have a cross filter in both directions. The image below contains the model and also the example data I've used:

Model

What we’re trying to do is create an overview of the amount of work planned per department in hours. So for this I’ve created a measure:

Total time in hours = (SUM(Projects[Amount]) * SUM(Routing[Time (in minutes)]) / 60

When I used this measure for a specific product, everything seems to be fine. However, when looking at the total time per departement I get a higher value. I'm not sure where this comes from. The table in the image shows the numbers I see in PowerBI. The total for Total time in hours (227) is much higher then the sum of the total time for the individual products:

Total time

Can anyone help me figure out how PowerBi calculates this total of 227?

Did his solution work or would you rather create a result set with a join? — Train
The solution works for this report, but I'd be interested in yours as well. Are you suggesting using a join in the measure? — Syvinna

greggyb greggyb · Accepted Answer · 2019-09-09T14:32:08

There is no link between 'Projects' and 'Routing'[Department]. So at your grand total level, SUM ( 'Projects'[Amount] ) => (5 + 5 + 5 + 5 + 4 + 4) => 28 and SUM ( 'Routing'[Time (in minutes)] => (186 + 301) => 487. Thus, your total is (28 * 487) / 60 => 13,636 / 60 ~=> 227.

Ultimately, the behavior is "correct" in that the DAX you've written is evaluating conforming to spec, but you'll likely need to take a different approach to the calculation.

One possible approach is:

New Total time in hours =
SUMX ( // SUMX iterates a table, and performs a calculation per row, collecting these
       // subtotals in a sum.
  VALUES ( 'Products'[Product ID] ), // VALUES gives us a table made up of unique values
                                     // in context from the named column (or table).
  [Total time in hours] // your already existing measure
)

PowerBI measure is giving an unexpected value

1 Answers