Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys.
Requesting assistance on the following:
I have a table where each unique person ID has hours entered against a week.
ID | Weeks since placement | Hours completed each week | Target hours to be completed each week |
a | 1 | 23 | 15 |
a | 2 | 21 | 15 |
a | 3 | 42 | 15 |
a | 4 | 23 | 15 |
a | 5 | 21 | 15 |
a | 6 | 23 | 15 |
b | 1 | 22 | 23 |
b | 2 | 21 | 23 |
b | 3 | 20 | 23 |
c | 1 | 15 | 30 |
c | 2 | 16 | 30 |
What I am trying to do is have a single record for an ID which shows the number of hours behind.
For example, for ID b, the person has completed 63 hours in total (22+21+20) where as the person should've done 69 according to target hours (23+23+23).
Is there a way I can have a single record which shows SUM(total hours completed) - (MAX(weeks since placement)*(Target hours to be completed))
The issue is, on the table it comes up as each row of unique of person with different weeks in each row. I wanted to summarize it in the format below
Solved! Go to Solution.
Hi @Anonymous
you can create a calculated table like
Table 2 =
ADDCOLUMNS(
DISTINCT('Table'[ID]);
"Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. -
Also I would do a behind pr week collumn - since this is also a good way to see a development if you plot it.
Also Since you have a Target pr week in each line - why not use that --- so if you ever encounter that it can warry over a project periond your calculations is still correct
Insert a new collumn with this formular if you just want the result:
Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID])))
New collumn Behind pr. week:
New collumn Behind pr. week:
Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week]
Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.
Untill you can write that yourself - I would ALWAYS reccomend having that in 3 or 4 sepperate rows Collumns, unless your data set already has more than Many rows and millions of lines. -
Also I would do a behind pr week collumn - since this is also a good way to see a development if you plot it.
Also Since you have a Target pr week in each line - why not use that --- so if you ever encounter that it can warry over a project periond your calculations is still correct
Insert a new collumn with this formular if you just want the result:
Behind total = CALCULATE(SUM('Table'[Target hours to be completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID]))) -CALCULATE(SUM('Table'[Hours completed each week]); FILTER('Table';'Table'[ID]=EARLIER('Table'[ID])))
New collumn Behind pr. week:
New collumn Behind pr. week:
Behind this week = 'Table'[Target hours to be completed each week]-'Table'[Hours completed each week]
Hope this help... but also hope you add more columns to better understand the formulars and calculatitions, this has helped me alot of times.
Hi @Anonymous
you can create a calculated table like
Table 2 =
ADDCOLUMNS(
DISTINCT('Table'[ID]);
"Hours Behind";CALCULATE(SUM('Table'[Hours completed each week]);ALLEXCEPT('Table';'Table'[ID]))-CALCULATE(MAX('Table'[Weeks since placement]);ALLEXCEPT('Table';'Table'[ID]))*CALCULATE(MAX('Table'[Target hours to be completed each week]);ALLEXCEPT('Table';'Table'[ID]))
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi AZ38. The solution worked to some extent, it gave me the following error:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Hi @Anonymous
It is a new calculated table (Modeling ribbon -> New table)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks @az38
Sorry I am quite new to Power BI. I clicked on the ribbon to make a New table enterring your formula, and got the same error, This is my formula that I had been using:
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |