Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mattbstrong
Helper IV
Helper IV

Working with Duplicate Data

I have the following data:

 

(Example 1)

Date--------Role---Project---Resource---Hours

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

 

There are a maximum of 2 values for Role, 2 values for Project, and 2 values for Resource for a total of 8 possible combinations on any given Date.

On any given Date, all the values of Hour for Boy will be the same and all the values of Hour for Girl will be the same.

 

I need to plot the number of hours for each day with one line for Boy and one line for Girl, and a third line that is the sum of Boy and Girl. However, if Boy appears more than once on any given Date, I only want his Hours to be counted once (not 4 times). I want the same thing for Girl.

 

Current result:

1/1/2020:

 - Boy 4 hours

 - Girl 8 hours

 - Total 12 hours

1/2/2020:

 - Boy 4 hours

 - Girl 12 hours

 - Total 16 hours

 

Deseired result:

1/1/2020:

 - Boy 1 hours

 - Girl 2 hours

 - Total 3 hours

1/2/2020:

 - Boy 1 hours

 - Girl 3 hours

 - Total 4 hours

 

Not all projects, roles or resources will appear on each day. I would expect to get the same desried results above from the data in example 2 below:

 

(Example 2)

Date--------Role---Project---Resource---Hours

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

 

Additional Question (this is not as important as the question above:

I would like to be able to drill down into the data. For example, if I plot the total hours for a day I would get the following:

 

1/1/2020:

 - Total 3 hours

1/2/2020:

 - Total 4 hours

 

This would appear as one line on a line chart. But then I want to be able to click on that line and have it show me two lines; one for the hours for role A and one for the hours of role B. The I would like to click on the line for role A and have it dispaly two lines; one for the hours of project Red and one for the hours of project Blue. Then I would like to click on the line for project Blue and have it show me two more lines; one for the hours for Boy and one for the hours for Girl.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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 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.

 

Hi,

I do not understand.  Make it simple.  Share a dataset with your required columns and show the expected result. 


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.

Hi,

I am still not clear.  Instead of focusing on the chart you want, focus on the output you want in a simple table format.  Once the figures in the table are correct, we just have to click on the Line chart visual to get our desired chart.  As you can see in the image below, i have changed your visuals to a simple table format.  Just let me know what exact figures are you expecting in the Table there.  If possible, you may include an MS Excel workbook with formula driven calculations.  I will translate those formulas into DAX formlas.


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

@Ashish_Mathur , I'm sorry I am not doing a good job explaining things. Maybe just try this.

Using the files I sent you, create a line chart. The chart should have Date on the x-axis and FTE-Total on y-axis. It should have two lines. One line is for Type AVL and the other is for Type PLN. Compare the values of AVL to the values in the table named Appended. AVL hours should always be very near 60 hours. What you are going to get are hours MUCH larger becasue hours are being summed more than once.

On any given Date, the AVL hours should be the sum of all the AVL hours for a Resource on that Date.

For example:

The Resource "Nita Ning" has an FTE-Total value of 0.9 when the Type is AVL.

On the Date 10/25/2020 he is listed 263 times.

When I plot "Nita Ning" on the Date 10/25/2020 he should have an FTE-Total value of 0.9. He should not have an FTE-Total value of 236.7 (this is the value you get when you mulitple 263 and 0.9).

@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?

amitchandak
Super User
Super User

@mattbstrong , Try a measure like

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.