Sum if hours over running 12 months are less than

[LotteA]

Hi everybody I still havent found anything near a solution. Is there anyone who can help me solve this? Or is it impossible in Power BI? 

If the screendumps are gone please see the full description here: 

https://docs.google.com/document/d/1lBaXQDBWsdIwQs5Xk0VfWzaXaRn2WVx-/edit?usp=sharing&ouid=105366691...

Hope someone can help me with this issue. I have been stuck for a month now and also wrote in december but did not find the answers I was looking for.

I have a report that calculates some average hours, days and in different ways for certain activities.

These are the  first 4 tabs in my file. Those are fine.

Then I need to do almost the same again but this time without those employees and their hours that have registered 220 hours or more on those activities within the last 12 months. The next two tabs shows a matrix with sum for all those under 220 and a list of thoose that has more than 220 hours. These work fine using the build in functions in the filter on the visual.

In the next tab I try to calculate the count of employees < 220 over 12 months and I am at a loss for how to make it work. I have tried building another table in Power BI with the sum of hours for easier calculations and counts. I can get the opposite count on how many has more, but it calculates for each row the total, instead of per enhed as it should. So if the Enheder should have the numbers 4, 2, 3, 4 it will write 13 in all rows. The extra columns are for calculation purposes.

I would expect this as I have calculated in the calculation excel file that I link to in the tab "Syg u. lang tid". In this excel fil the calculations for all employees are on the first tab and in the second "Syg u. lang tid" I have all the calculations again, but with the removed lines from those with more than 220 hours over 12 months.

In the next tab "? Fravær gns i timer < 220" I am trying to calculate the average pr unit pr month. Easy in excel, as the line are removed. In Power bi I cant get it right with the <220 as part of it. I have tried many different approaches.

I would except this in the top in UL Fravær gennemsnit Sum/number of emoyees per department with less than 220 hours

And this in the bottom for october, the sum for the last 12 months  for each department on the results of the above calculation.

Finally in the last tab

In the first matrix I would expect these results for september and october and the fx is the total for the 12 month calculated before divided by 7.4

In the next it is the total of all hours on the activities per month divided by all that has registered hours again only if the sum is less than 220 over 12 months.

I would expect this result:

Line 180 in the excel tab 2

Then ít is the sum of the running 12 months of the previous calculation and it is way of here.

I would expect this For october

And the most impotant of them all that is a must for the report is the final calculation for all. This is the one that works, but I need it with the <220 hours for the last 12 months

I would expect this result:

And it is the result from line 180 in excel divided with 7.4

Once I have all theese calculations i need to make a visual like this:

I hope it makes sense and someone can help.

I apologize up front if my pbix is a bit messy. I have been forth and back so many times , so many measures and trials with no success.

The links to the files are

Pbix: https://drive.google.com/file/d/1sCDpWnVn8r_1bNMSGWFBwOdXniS8_IPM/view?usp=sharing

Calculations: https://docs.google.com/spreadsheets/d/1y2J7lk5ndU7Sn-EaPjk84Z3Qc10Kir_C/edit?usp=sharing&ouid=10536...

Data for the pbix: https://docs.google.com/spreadsheets/d/1QqpnqlKM_cYk-6IoMsHnhfVFHU7TMEhX/edit?usp=sharing&ouid=10536...

Best regards Lotte

[Anonymous]

Hi @LotteA ,

The aggregation of a matrix over rows presupposes that the columns on the rows are related to each other. But from your pbix file, it appears that the matrice does not grouped by Sektion. This may be what is causing your measure to return the same value. This is because the measure is only calculated by time, but not by type of Sektion. I suggest you try using MAX or SELECTEDVALUE at DAX to get the current Sektion value and filter by Sektion.

Best regards,

Mengmeng Li

[LotteA]

I have updated in my none anonomous file with the datamodel, and it did not help. Debugging shows that it finds the correct Sektion, but the issue is when it should connect initialer from the EO sum table through Bruger to Registreringer, somthing goes wrong. I hope someone can help me?

Best regrds Lotte

[Anonymous]

Hi @LotteA ,

After my investigation, I realized that when I put the same columns in the matrix in page "? Medarbejdere < 220" as in the matrix on page "Fravær < 220", the matrix on page "? Medarbejdere < 220"1 displays incorrectly. But the matrix works fine in the new created page. Did you do some settings on page "? Medarbejdere < 220"?

Also, I would suggest that it would be better for you to change the data type of the Registreringer[EnhedId] to whole number, since the Enhed[EnhedID] with which it is related has a data type of whole number.

Best regards,

Mengmeng Li

[LotteA · Accepted solution]

In case anyone is interested in a follow up. 

After many trials and errors, I had contact with some specialist i Power BI. The result was, it cannot be done in this report in Power BI, due to complex contexts. 

I solved it by doing the needed calculations in SQL and importing them as new data for each calculation and made them a part of the datamodel. So I ended up with the needed result, but had to do some of it with SQL.

[LotteA]

Hi @Anonymous 
Thank you for spendning time on my question again. First the datatype was a short attempt on something previous. And it made no difference in my results. But yes it should be a whole number.

The page Fravær < 220 is set up with sum of hours and with no calculations in the matrix, the build in filter "less than 220" in the filter pane works fine. The page Medarbejdere < 220 is an attemp to count the number of employees that has worked in the periode, but has not had more than 220 hours in the activities chosen. I cannot see that there should be any other difference. If I try with the same columns in Fravær < 220 as in medarbejdere < 220 FRavær shows wrong. If I try with columns from fravær in medarbedere it works like fravær. So to me it looks like the same. Or am I misunderstandig you?

Best ragrds Lotte

[LotteA]

Thank you for your reply. I am looking into it now. In the datamodel I had forgotten to connect my latest  sum table. I am not sure if this was the issue, as I had the same issue with the first sum table I created, and this was connected. 

[LotteA]

I do not know why it leaves out most of the screen dumps