Calculated sum from another table

[Henrik_99]

Hi everyone,

I am making a report in Power BI. I am trying to get used hours per area (Id); building; group(no). I have two tables. The way my data is formatted there are two tables, one (table a) that has used hours with each representing the value of one id per project within one Yearweek.

The other (b) (main report) has the ordre, weight status, phase and general information. The tables have a relationship throught the ID column, with the one side being the Used hours and the many being the main report with ordre, weight. Table b is containing less amount of ID`s than table a.

This is what I have written so far in main report table:

Usedhours_measure = CALCULATE(SUM(HOURS[USED_HOURS]);FILTER(HOURS;HOURS[Group]=VALUES(Ordre[Group])))

It does give correct sum for group and give an error if I want to show several groups, how do you write dax to sum used order dynamically per building or per phase or per group from main report.

Used hours (a):

Main report (b):

 Relations:

Show example main report:

[Henrik_99]

Thanks, that helps a bit further.

UsedHours_Buildingblock = CALCULATE(SUM(AA_RV_KPI_HOURS_6106[USED_HOURS]);FILTER(AA_RV_KPI_HOURS_6106;AA_RV_KPI_HOURS_6106[Building]=MAX(ReportResult09[Building])&&AA_RV_KPI_HOURS_6106[Discip]=MAX(ReportResult09[Discip])))

However, the table is not showing expected total sum as shown below. Feedback will be appriciated, thanks, rgds H

[v-chuncz-msft · Accepted solution]

@Henrik_99,

You may use a similar way as shown here.

[Henrik_99]

Thank you, v-chuncz-msft!

Rgds

Henrik

[v-chuncz-msft]

@Henrik_99,

Here you may use MAX Function instead of VALUES Function.