cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Restar valores acumulados en tablas de Recursos Humanos

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 

 

KevinMSi_0-1596634799579.png

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

KevinMSi_1-1596635414637.png

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

 

KevinMSi_2-1596635499740.png

 

Desde ya, gracias,

Kevin

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Restar valores acumulados en tablas de Recursos Humanos

Hi @KevinMSi ,

 

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.

MFelix_0-1596669081935.png

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Community Support
Community Support

Re: Subtract accumulated values from HR tables

Hello @KevinMSi ,

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

R1.jpg

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])

R2.jpg

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

R3.jpg

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

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

R4.jpg

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.

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

Re: Restar valores acumulados en tablas de Recursos Humanos

Hi @KevinMSi ,

 

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.

MFelix_0-1596669081935.png

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Community Support
Community Support

Re: Subtract accumulated values from HR tables

Hello @KevinMSi ,

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

R1.jpg

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])

R2.jpg

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

R3.jpg

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

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

R4.jpg

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.

View solution in original post

Highlighted
Regular Visitor

Re: Restar valores acumulados en tablas de Recursos Humanos

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

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors