Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Trying to Add External Data available to create a Measure

Hi, I am trying to calculate the available hours from our technicians, for this we have the all kind of  Hours record in a table from our ERP ( PowerBi is directly connected to it) and outside the system added to PowerBi  the available  hours for the year in an excel table.

 

I tried in two ways :

1 - Add a Excel table With the month and the Calendar Available Hours for each Month

2 - Add a Excel table with the Tech Name, Month and Available days for each one

I Have managed to create a measure for each month adding manually the Available hours and substracting to this number the number of  no presence Hours with this formula

Available Hours January = SUMX(SUMMARIZE(JEG,JEG[Técnico],"Horas",168 - [NO PRESENCE]), [Horas])
But i have to create a measure this way for each month and for each Year, so if you change the date to check the data you have to change the selection in the tables to match the right hours
The ERP file that rules all the report has all the hours recorded and categorized like the first column "Codigos Horas"
And with this categorizations i created the measures that build the report
Non Billed Hours = CALCULATE(SUM(JLE_JSB[Horas Totales]),'Tipos Horas'[Tipo]="No Facturadas")
OT's Hours = CALCULATE(SUM(JLE_JSB[Horas Totales]),'Tipos Horas'[Tipo 2]= "Horas en Ots")
No Signed Hours= [Available Hours January ] - [No billed Hours] - [OT's Hours]
Any idea how to do it automatically ?
Thanks and Regards

 

Alejandro

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi  Anonymous ,

     

    How is the ERP file setup for the hours calculation? You present the classification but the question I posing is if there are in that file the additional data as tech, number of hours and date?

     

    Based on the overal request you should not have the need to add the tech/available hours per month table since to what I can see everyone has the same number of hours available so the firts table that you present is sufficient to make it dynamic.

     

    Can you share a small sample of data?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi! Sorry for my late response!

      For the Hours calculation i created a calculated column that  joined  the worked  hours and the Km transformed to hours

      in the Horas Totales Column because  are used to count as work hours.

      Here  you have an example of the tables , etc i use, please let me know if you need more info.

       

      LEEEIDLEDATWLELABCLEDDESHoras Totales
      75520190103VAVACACIONES8
      21420190128

      FO

      		FORMACION8
      2022019012454HORA CAMPO8
      2022019012554HORA CAMPO8
      8102019012955HORA TALLER8
      8102019013055HORA TALLER8
      8812019020655HORA TALLER8
      8812019011835KILOMETROS7,5
      8102019010838KILOMETROS3,125

      This is the JLE_JSB Table

      And this is the JEG Table where the Technicians are recorded

      EGGEEIDTécnico
      755Javier Cobas
      810Joaquin Cerecedo
      881David Caballero
      214Aida Rodriguez
      202Juan Pereira

       

      I Also have the Calendar tables explained in the previous post, if the first table is ok, perfect.

      And here also you have  the relations between the different tables

      And here you have the measures that i have created

      The Idea is to substitute this measures for other that automatically calculate The available hours and the non signed hours when selecting the date in filter created for that

      Many Thanks and Regards