Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
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.
Muchas gracias, me sirvió bastante la solución, los valores ahora corresponden a lo calculado.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |