Restar valores acumulados en tablas de Recursos Humanos

[Anonymous]

Hola a todos, espero puedan ayudarme, llevo un par de días buscando ordenar información de personal de una empresa y su evolución en el tiempo.

Tengo dos tablas con valores acumulados de personas desempeñandose en determinado cargo, uno considera la fecha de ingreso y otra la fecha de retiro según 

Las formulas usadas van de acuerdo al Quick Measure - Running Total (filtrado por año) para ambas tablas pero cuando quiero restar los valores para poder tener un valor neto no tengo el valor requerido.

Por ejemplo en 2020

Número de "MECANICO I" netos = Número de "MECANICO I" entrantes (20) - Número de "MECANICO I" saliente (10) = 10 pero el valor que me arroja la tabla es 19

Trabajo actualmente con dos tablas ligadas por un campo de año como referente.

Desde ya, gracias,

Kevin

[MFelix · Accepted solution]

Hi @Anonymous ,

What is the column year you are using for the final matrix? In cases where you have 1 to many relationships you need to use the column from the table on the 1 side of the relationship.

Appears to me that you are using the column for year from the other table also since you have the Cargo that appears on both tables you need to setup also a dimension table using that column.

So if you create a table with distinct values for cargo and make the same relationship has you have on the year and then use the two dimension tables as rows and columns on your matrix the result should be as expected.

As you can see in the image below using the cargo from one of the table the values are all equal in all columns, making the dimension table cargo the results are correct.

Check PBIX file attach.

[v-zhenbw-msft · Accepted solution]

Hello @KevinMSi ,

Do you want to subtract the value from the In table from the out table value?

If yes, you can refer to the following steps.

1. Create a single load table and create one-to-many relationships with the out table and in the table.

Cargo table = VALUES(Out[Cargo])

2. Then we can create a matrix table using a new load table and years table.

3. At last we can create a measure and put it in Values.

Measure = CALCULATE(SUM(Out[value])) - CALCULATE(SUM('In'[value]))

If you do not meet your requirements, could you provide a mock-up sample based on false data?

It will be useful if you can show us the exact expected result based on the tables.

Upload your files to OneDrive for Business and share the link here. Please do not contain any Confidential Information or Real Data in your response.

Best regards

Community Support Team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

Muchas gracias, me sirvió bastante la solución, los valores ahora corresponden a lo calculado.

[MFelix · Accepted solution]

Hi @Anonymous ,

What is the column year you are using for the final matrix? In cases where you have 1 to many relationships you need to use the column from the table on the 1 side of the relationship.

Appears to me that you are using the column for year from the other table also since you have the Cargo that appears on both tables you need to setup also a dimension table using that column.

So if you create a table with distinct values for cargo and make the same relationship has you have on the year and then use the two dimension tables as rows and columns on your matrix the result should be as expected.

As you can see in the image below using the cargo from one of the table the values are all equal in all columns, making the dimension table cargo the results are correct.

Check PBIX file attach.