Forum Discussion
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.
8 Replies
Anonymous , Try a measure like
sumx(summarize(Table, Table[Date], Table[Resource], "_hr",max(Table[Hours])),[_hr])
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.
