Wrong row totals

[Anonymous]

Hi all,

I'm looking for some help getting the right row totals. The situation: Employees in our company have to register their worked hours in the booking system. Details about which company they work for are available in the HRM system, for that reason I had to map them in "MASTER - Employees". Last year a lot of employees switched from Company A to Company B, for that employees Power BI calculates the wrong row total.

Below is an example of one employee who switched from Company A to Company B in March 2018.

Relationship between the tables:

And the measure I used so far:

Test = 
CALCULATE(
    DIVIDE(SUM('FACT - Entries'[Tijd in minuten]);60;BLANK());
    FILTER(
        'FACT - Employment Records';
        'FACT - Employment Records'[Start Date] <= MAX('DIM - Calendar'[Datum])
        && 'FACT - Employment Records'[End Date] >= MIN('DIM - Calendar'[Datum])
    )
)

Who can help me to solve this problem? Many thanks!

[v-shex-msft]

Hi @Anonymous ,

I think you need to add if statement to check current row contents level and write a specific calculation formula with conditions based on current category fields, it will help measure to group calculation result by current category.

Measure Totals, The Final Word

If you still confused on coding formula, please share some sample data for test.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @v-shex-msft,

Thank you for your reply. The solution was to create an extra measure:

Measure2 = SUMX(VALUES('DIM - Calendar'[Datum]);[Test])