Do all calculations for only employees with less than 220 hours within the last 12 months

[LotteA]

Dear experts

Last week I got some solid help to solve an isse with calculating some average time registration things. 

Now I am in need of help again, as I need to do almost the same calculations, but now only for employees with less than 220 hours for specific projects.

It is both when calculating sums of hours, number of employees and so forth, that it should only look to those with less than 220 hours for 5 specific projects.

I have tried with simple filtering, and different kind of measures. I am attaching links to my data and file.

In the report my challenges are:

Tab U. lang fravær It calculates correct for those with , but only for the latest month. It would be nice to either get the calculations right for each month or just show latest month. I suspeck it has something to do with my slicer, but it is that, that decides how many months the calculation should be for. (Nice to have)

Tab U. Lang gns. fravær I am trying to generate the avarage number of hours pr month pr section for those employees with less than 220 hours on specific projects, and the division with number of employees should also only be the number that has less than 220 hours registreret (Nice to have)

Mock code: DIVIDE(SUMX(Registreringer, Registreringer[Sum Timer]<220), count(Registreringer[Antal medarbejdere], where sum timer < 220))+0 (Nice to have)

And also in this tab the same calculations but for running 12 months. (Nice to have)

Last but most importantly as I can live without the other calculation if these two are correct:

Tab U. Lang GNS. fravær total

Gennemsnitligt fravær over løbende 12 måneder pr. enhed i dage No matter what I have tried it gives me zero or the same as before the less than 220 hours.

The same goes for my total Samlet sygdom løbende 12 mdr.

The result of this tab should be at least for september and october:

Any help would be greatly appreciated. I hope I have explained the issue well enough.

Links to files are: 

https://docs.google.com/spreadsheets/d/1B2L1LHvWFyN_-Kc_jH-Dsv_Jnkg0IBPX/edit?usp=sharing&ouid=10536...

https://drive.google.com/file/d/11_Tt-FAh7Nq7S4tqsVM8KUU8S1_csolg/view?usp=sharing

Kind regards

Lotte

[LotteA]

Hi @v-yiruan-msft 

Thank you so much for taking time to help me. 

It is not giving the right result. It seems like it calculates some wild totals and they are of.

I have done the calculations in excel and the results should look something like:

Running 12 month Sum (Hours) Where Running 12 month Sum < 220

Running 12 month Sum (Hours) Where Running 12 month Sum < 220 Divided with total number of employees

The last calculation is the total in this list on the second line (Afdeling 4)

The green total is the total sum for the company calculated by taking the running 12 month sum of the top line in belov screendump and divide by 7.4.

The top line is the total sum of all hours divided with number of employees total (<220)

The second line is just the total number of hours <220 

I can easily do the calculations in excel, but it is a manual process of cleaning the data each time and make the calculations, so it would be so great if it could be a Power BI report, that calculated dynamically each month.

Again any help would be appreciated, thank you.

Kind regards Lotte

[v-yiruan-msft]

Hi @LotteA ,

Which measure are used for the values in the below screenshot? Could you please provide the calculation logic for them? Thank you.

Best Regards

[LotteA]

@v-yiruan-msft 

Hi again, thank you so much for getting back to me. 

The calculations are not measure it is done in excel. I have the list of hour / timer and remove the lines of those employees / Medarbejdere that has more than 220 hours. Then it is a simple sum of total hours in the bottm line and the top line is this sum diveded with total number of employees, that has hours between 0 and 220.

So the results I have shown as screendumps are all from excel. But In the power BI report in the first four tabs, theese calculations has been made and are correct but just for all employees no matter how many hours. My difficulties is to do the same calcultions as those three tabs, but only for employees with less than 220 hours. 

Kind regards Lotte

[v-yiruan-msft]

Hi @LotteA ,

Please check if the visual calculation can help achieve your requirement.

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Best Regards

[LotteA]

I cannot get this to work either unfortunately

[v-yiruan-msft]

Hi @LotteA ,

There are 7 pages in the pbix file, first 4 pages are correct with full data. And you want to get the values which only include the hours less than 220 for the left 3 pages? Could you please provide the expected result for these 3 pages in the excel file base on the current sample data? It would be very helpful to get the solution. Thank you.

Best Regards

[LotteA]

Hi @v-yiruan-msft 

It seems impossible. I have spend many hours with different ai's to try and get a solution. But I end up all the time with wrong results.

In words it is as simple as I need a measure that filters out employees based on total hours worked over the last 12 months and only show those who have worked less than 220 hours in total across specific activities. I believe if I can create this, I can get the other calculations to show the correct results. I am testing it in the matrix that shows each month. U. lang fravær.
But trying to do y´this I either end up with employees in the list that has more than 220 hours on the activities in total for a year or I end up with only 3 where there should be many more.
Do you have any suggetstions how to approach this differently? 
The measures I have been looking at today is:

Sum12Mdr_ActivityFiltered =
VAR Last12Months =
DATESINPERIOD(Dato[Dato], MAX(Dato[Dato]), -12, MONTH)

VAR TotalHours =
CALCULATE(
[Sum Timer], 
Last12Months, 
'Emner'[Nr] IN { "990208500", "990220000", "990212000", "990211000", "990209000", "990208000", "990207000" } 
)
RETURN TotalHours

And then EmployeeRegistrationsUnder220 =
SUMX(
FILTER(
'Registreringer', 
CALCULATE(
[Sum12Mdr_ActivityFiltered],

ALLEXCEPT('Registreringer', 'Registreringer'[Initialer]) 
) < 220 
),
[Sum Timer] 
)

And last EmployeeMonthlyRegistrationsUnder220 =
SUMX(
FILTER(
'Registreringer', 
CALCULATE(
[Sum12Mdr_ActivityFiltered], 
ALLEXCEPT('Registreringer', 'Registreringer'[Initialer]) 
) < 220 
),
CALCULATE(
[Sum Timer], 
'Emner'[Nr] IN { "990208500", "990220000", "990212000", "990211000", "990209000", "990208000", "990207000" } 
)
)

Kind regards Lotte

[LotteA]

@v-yiruan-msft Hi,

Here is the excel sheet where the original calculation has been done. https://docs.google.com/spreadsheets/d/1_83gpOofzCBhkzmLrpau0Hh-qELCnXtP/edit?usp=sharing&ouid=10536...

It is this I have been asked to make as a Power BI report.

The tab syg, corresponds to the first four tabs, where I have made different matrixes to show the calculations going into the the last grand total that for October were 10,46 if you remove Afdeling 23, that was not part of the Excel sheet data originally.

The tab Syg u. lang tid is the same calculations but 13 lines has been manually removed because they were 220 hours or above. These are in columns S and T from row 17. The calculations are the same as before now just without those lines and the grand total for October should be 6,10 again without afdeling 23.

So yes my U tabs shown here: 

should be the same as the ones before, shown here:

Just with those 13 lines removed because they are 220 hours or above.

On the U. lang. GNS. fravær Totaler I have removed some of the calculations, because I thought they would be to difficult to make with the filter. And as I wrote at first the totals are my need to. These I have marked in the titel below. The other two matrixes and the ones on the tab U. lang. GNS. fravær enhed are all nice to have. But I hope, when I know how to get the thirteen lines filtered out  that it would be easy to show all the matrixes.

Thank you very much for helping me. 

Kind regards Lotte

[v-yiruan-msft]

Hi @LotteA ,

I updated your sample pbix file(see the attachment), please check if that is what you want. Please update the following 2 measures as below:

Langtidssyge oversigt =
VAR _sumtier = [Sum Timer]
RETURN
    SUMX ( FILTER ( Registreringer, _sumtier > 220 ), [Sum Timer] )

UL Gennemsnit fravær sektion = DIVIDE ( [UL Sum Timer pr mdr], [Antal_medarbejdere] ) + 0

Best Regards

[LotteA]

@v-yiruan-msft PS. Afdeling 23 is not part of the totals in the Excel calculations.