cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Reply
Helper III
Helper III

Trabajar con datos duplicados

Tengo los siguientes datos:

(Ejemplo 1)

Fecha--------Role---Proyecto---Cursto---Horas

1/1/2020---A------Red-------Boy---------1

1/1/2020---A------Red-------Girl---------2

1/1/2020---A------Blue-------Boy---------1

1/1/2020---A------Blue-------Girl---------2

1/1/2020---B------Red-------Boy---------1

1/1/2020---B------Red-------Girl---------2

1/1/2020---B------Blue-------Boy---------1

1/1/2020---B------Blue-------Girl---------2

1/2/2020---A------Red-------Boy---------1

1/2/2020---A------Red-------Girl---------3

1/2/2020---A------Blue-------Boy---------1

1/2/2020---A------Blue-------Girl---------3

1/2/2020---B------Red-------Boy---------1

1/2/2020---B------Red-------Girl---------3

1/2/2020---B------Blue-------Boy---------1

1/2/2020---B------Blue-------Girl---------3

Hay un mรกximo de 2 valores para Role, 2 valores para Project y 2 valores para Resource para un total de 8 combinaciones posibles en cualquier fecha determinada.

En una fecha determinada, todos los valores de Hour for Boy serรกn los mismos y todos los valores de Hour for Girl serรกn los mismos.

Necesito trazar el nรบmero de horas para cada dรญa con una lรญnea para Boy y una lรญnea para Girl, y una tercera lรญnea que es la suma de Boy and Girl. Sin embargo, si Boy aparece mรกs de una vez en una fecha determinada, sรณlo quiero que sus horas se cuenten una vez (no 4 veces). Quiero lo mismo para Girl.

Resultado actual:

1/1/2020:

- Niรฑo 4 horas

- Chica 8 horas

- Total 12 horas

1/2/2020:

- Niรฑo 4 horas

- Chica 12 horas

- Total 16 horas

Resultado deseado:

1/1/2020:

- Niรฑo 1 horas

- Chica 2 horas

- Total 3 horas

1/2/2020:

- Niรฑo 1 horas

- Chica 3 horas

- Total 4 horas

No todos los proyectos, roles o recursos aparecerรกn en cada dรญa. Esperarรญa obtener los mismos resultados derivados anteriores de los datos en el ejemplo 2 a continuaciรณn:

(Ejemplo 2)

Fecha--------Role---Proyecto---Cursto---Horas

1/1/2020---A------Red-------Boy---------1

1/1/2020---A------Red-------Girl---------2

1/1/2020---A------Blue-------Boy---------1

1/1/2020---A------Blue-------Girl---------2

1/2/2020---B------Red-------Boy---------1

1/2/2020---B------Red-------Girl---------3

1/2/2020---B------Blue-------Boy---------1

1/2/2020---B------Blue-------Girl---------3

Pregunta adicional (esto no es tan importante como la pregunta anterior:

Me gustarรญa poder profundizar en los datos. Por ejemplo, si ded cรกlculo el total de horas para un dรญa, obtendrรญa lo siguiente:

1/1/2020:

- Total 3 horas

1/2/2020:

- Total 4 horas

Esto aparecerรญa como una lรญnea en un grรกfico de lรญneas. Pero entonces quiero ser capaz de hacer clic en esa lรญnea y que me muestre dos lรญneas; uno para las horas para el rol A y otro para las horas del rol B. Me gustarรญa hacer clic en la lรญnea para el rol A y hacer que muestre dos lรญneas; uno para las horas del proyecto Rojo y otro para las horas del proyecto Blue. Entonces me gustarรญa hacer clic en la lรญnea para el proyecto Azul y que me muestre dos lรญneas mรกs; uno para las horas para Boy y otro para las horas para Girl.

1 ACCEPTED SOLUTION
Super User III
Super User III

Hola

Puede descargar mi archivo PBI desde aquรญ.

Espero que esto ayude.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Super User III
Super User III

Hola

Puede descargar mi archivo PBI desde aquรญ.

Espero que esto ayude.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@Ashish_Mathur, esto funcionรณ en parte. Al tratar de simplificar mi ejemplo, dejรฉ fuera una columna de datos. He aรฑadido los datos y se puede descargar un archivo en haga clic AQUI.

Si nos fijamos en los grรกficos de lรญneas verรก mis problemas.

Ejemplo 1: Horas por fecha y tipo

El horario de AVL debe ser de 6 cada dรญa.

Las horas de PLN son correctas a las 16 cada dรญa.

Ejemplo 2: Horas por fecha y rol

Las horas de AVL para A deben ser de 6 cada dรญa.

Las horas de AVL para B deben ser de 6 cada dรญa.

Ejemplo 3: Horas por Dat y Proyecto

Las horas AVL para azul deben ser 6 cada dรญa.

Las horas de AVL para rojo deben ser 6 cada dรญa.

Ejemplo 4: Horas por fecha y recurso

Las horas de AVL para niรฑo deben ser 2 cada dรญa.

AVL Hours for Girl debe ser 4 cada dรญa.

Hola

No entiendo. Hazlo simple. Comparta un conjunto de datos con las columnas necesarias y muestre el resultado esperado.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur , I have placed new zip file HERE.

 

I included all 3 source Excel files and the Power Bi File.

 

On the reports:

The top line chart should show the total AVL and PLN hours by Date. (One line for PLN and one Line for AVL.)

The bottom stacked area chart should show the total PLN hours by Date grouped by Data Source. (There are three data sources so there shoul be three stacked areas.)

 

Calculating PLN hours on a given Date:

Toal PLN hours on 1/1/2020 = Sum of all the PLN hours for all the Projects on 1/1/2020.

Project PLN hours  = Sum of all the PLN hours for all the Roles on 1/1/2020.

Roles PLN hours = Sum of all the PLN hours for all the resources on 1/1/2020.

Resource PLN hours = A given value in the table on 1/1/2020 (these are not calculated).

 

Calculating AVL hours on a given Date:

The AVL hours are calculated in the same way as the PLN hours are calcuated BUT the AVL hours for a resource should only be counted once on any given Date no matter how many times they are listed on that date. For example, if on 1/1/2020 a Resource is working on two different projects, they will be lsited twice on 1/1/2020. The AVL hours on that date for that resource should only be counted once.

Hola

Todavรญa no lo tengo claro. En lugar de centrarse en el grรกfico que desee, concรฉntrese en la salida que desee en un formato de tabla simple. Una vez que las figuras en la tabla son correctas, sรณlo tenemos que hacer clic en el grรกfico de lรญneas visual para obtener nuestro grรกfico deseado. Como se puede ver en la imagen de abajo, he cambiado sus objetos visuales a un formato de tabla simple. Sรณlo avรญsame quรฉ cifras exactas esperas en la Mesa. Si es posible, puede incluir un libro de MS Excel con cรกlculos basados en fรณrmulas. Traducirรฉ esas fรณrmulas en formlas DAX.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur , I'm sorry I am not doing a very good job explaining things. I have gone really simple this time. Please click HERE and download the file "Very Simple Rev 1".

 

The line chart is not displaying what I need.

The PLN line is correct. It plots the value of 3.5 FTE for all four days. It sums all the FTE of Type PLN on each day.

The AVL line is not correct. It plots the value of 12 FTE for all four days. It is summing all the FTE of Type AVL on each day. I want it to plot the value of 3 FTE on each day. All the FTE Values of Type AVL on a given day will always be the same. I want that single FTE value on that day, not the sum of all the FTE values on that day.

 

Explination:

  • AVL stands for Available. This is the number of hours in a day that a resource is available to work.
  • PLN stands for Planned. This is the number of hours in a day that a resource is planned to work.
  • PLN should always be a sum of all the FTE enteries for a Resource on a given day.
  • AVL should never be a sum of all the the FTE enteries for a Role on a given day. It should just be the FTE value for that day.
    • NOTE: The AVL FTE for a Resource on any given day will always be the same value.

Example: 

  • In the attached file, the Resource "Boy" shows up 4 times every day because he is working on 4 different preojects. Regardless of how many times he shows up on a day, he is still only one person and is only availble 3 hours a day.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur , this is fantastic! It works very well. Now I want to ask a little more complicated question. Please download the REV 2 file HERE.

 

This example worked well when there was only one value for Resource. Now I need to figure out how to do it when I have two Resources. I duplicated the 32 rows from REV 1 and chnaged only the Resource on those new rows from "Boy" to "Girl". I need the data in the line charts to now double.

 

For Example:

  • There are now two resources that each have 3 AVL hours each day.
  • I now need the line chart to show a total for 6 AVL hours each day.
  • The PLN hours are working. They doubled as expected. They also update if I change the Resource filter.

 

Additional Note:

I need to be able to filter by Resource and have both the AVL and PLN hours update appropriately.

In the future, there will also be many different Roles. At that time, I need to have the line chart dispaly the correct data if I filter on Role and Resource.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur , Thank you for your file. I looked at it and it final helped me understand your method for solving my problem. However, I don't think looking for "Min" values is going to work. I decided to start over with an entirely new approach. I made multiple tables and linked them all together. It worked really well. You can find the files HERE if you are interested in seeing how I solved the problem. Download both the Excel file and BI file for REV 3.

 

Thank you for your help!

@Ashish_Mathur , I am new to Power BI. Is a data set different than what is downloadable at the link I provided in my last message?

@Ashish_Mathur , this worked partly. In trying to simplify my example I left out a column of data. I have added the data and you can download a file at by click HERE.

 

If you look at the line charts you will see my problems.

 

Example 1: Hours by Date and Type

AVL Hours should be 6 each day.

PLN Hours are correct at 16 each day.

 

Example 2: Hours by Date and Role

AVL Hours for A should be 6 each day.

AVL Hours for B should be 6 each day.

 

Example 3: Hours by Dat and Project

AVL Hours for Blue should be 6 each day.

AVL Hours for Red should be 6 each day.

 

Example 4: Hours by Date and Resource

AVL Hours for Boy should be 2 each day.

AVL Hours for Girl should be 4 each day.

 

Super User IV
Super User IV

@mattbstrong , Pruebe una medida como

sumx(summarize(Table, Table[Date], Table[Resource], "_hr",max(Table[Hours])),[_hr])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors