Forum Discussion
Helper IHow can i group after summarize
Hello everyone,
I have a table “Records” with training hours by worker (badge, course id and hours):
badgeID | Course | Hours |
242673 | 2729 | 42 |
173573 | 2917 | 4 |
173573 | 3057 | 8 |
240077 | 2730 | 42 |
243575 | 3090 | 1 |
242036 | 3090 | 1 |
243272 | 3090 | 1 |
240047 | 2833 | 14 |
242926 | 2830 | 25 |
242297 | 2908 | 7 |
240524 | 2830 | 25 |
To calculate the total training hours by worker:
TotalTrainingHours =
SumX(
SUMMARIZE(
Records,
Records [badgeID],
"TotalHrs",SUM(Records[Hours])
),[TotalHrs])
My headache is this: I have to group by Total hours :
TotalHrs | Distinct workers |
<=20 | How many? |
<30 | How many? |
>=30 | How many? |
If anyone could help, i would be very grateful.
Thank you in advanced,
Pedro
Built 2 calculated columns and one measure in the data model given.
Columns on tabWorker:
Training Hours Per Worker = CALCULATE( SUM( tabData[Hours] ) )TotalHrs Label = SWITCH( TRUE(), tabWorker[Training Hours Per Worker]<=20, "<=20", tabWorker[Training Hours Per Worker]<30, "<30", tabWorker[Training Hours Per Worker]>=30, ">=30" )Measure:
Worker Cnt = COUNTROWS( tabWorker )give it a thumbs up and accept as solution if this helps!
4 Replies
- YukiK
Impactful Individual
It's easier when you have a different table with columns badgeID and summed hours per worker. You can create a table like this:
ADDCOLUMNS(
SUMMARIZE(
Records,
Records [badgeID]
),
"TotalHrs",SUM(Records[Hours])
)
Then I'd just create a calculated column to lable each worker with values you mentioned (e.g. <=20)
YourColumns =
SWITCH(
TRUE(),
Table[TotalHrs]<=20, "<=20",
Table[TotalHrs]<30, "<30",
Table[TotalHrs]>=30, ">=30",
)
titusThk you YukiK for your time.
That's not quite.
without wishing to waste your time i share my pbix and source:
https://1drv.ms/u/s!AgewLjPenPlMi99JTVSFLze50JbHyg?e=4BXUmI
(i'm not able to attach any file)
everything is working fine, but i'm stuck in that measure: how many workers by Total Training hours category
Again, thk U YukiK
- YukiK
Built 2 calculated columns and one measure in the data model given.
Columns on tabWorker:
Training Hours Per Worker = CALCULATE( SUM( tabData[Hours] ) )TotalHrs Label = SWITCH( TRUE(), tabWorker[Training Hours Per Worker]<=20, "<=20", tabWorker[Training Hours Per Worker]<30, "<30", tabWorker[Training Hours Per Worker]>=30, ">=30" )Measure:
Worker Cnt = COUNTROWS( tabWorker )give it a thumbs up and accept as solution if this helps!
- titus
Excelent!
THK U so much!
It's great.