Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
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
Hi @v-shex-msft,
Thank you for your reply. The solution was to create an extra measure:
Measure2 = SUMX(VALUES('DIM - Calendar'[Datum]);[Test])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |