Forum Discussion
Measure - Average time per category per task
Hello all,
I'd like to build stacked chart with Average resolution time for category per month per Task distinct count. Here is my data in Table ForAnalysis:
| ForAnalysis | |||
| Task | Month | Category | Time |
| N1 | Jan-20 | 1 | 5 |
| N1 | Jan-20 | 2 | 1 |
| N1 | Jan-20 | 3 | 3 |
| N2 | Jan-20 | 1 | 5 |
| N2 | Jan-20 | 1 | 2 |
| N2 | Jan-20 | 2 | 4 |
| N3 | Feb-20 | 1 | 6 |
| N3 | Feb-20 | 2 | 1 |
| N3 | Feb-20 | 1 | 3 |
| N3 | Feb-20 | 3 | 5 |
| N4 | Feb-20 | 1 | 2 |
| N4 | Feb-20 | 2 | 3 |
| N4 | Feb-20 | 1 | 2 |
| N4 | Feb-20 | 3 | 1 |
| N4 | Feb-20 | 3 | 1 |
The result per category and distinct Task number per month:
| Average | |
| Jan Catego 1 | 6 |
| Jan Catego 2 | 2.5 |
| Jan Catego 3 | 3 |
| Feb Catego 1 | 6.5 |
| Feb Catego 2 | 2 |
| Feb Catego 3 | 2.33 |
Here is how I want to calculate it: Category 1 has 3 row / value in January meaning this is 5+5+2 = 12 and now I divide it per distinct Task count in January 12 / 2 = 6. This is how I come up with my average category per month per task. Respectively for other Categories.
Seems simple but somehow I can't figure it out trying different Measures based on Averages, CALCULATE and few others.
Any help much appreciated.
Thanks Evo
Anonymous
I think the easiest way would be to, simply, add a quick measure "Weighted Average per Category" . I tested with your sample data, and confirmed that it genrates the expected results. Hope it helps.
OR
Then, select "Weighted Average per category" from the dropdown, and fill out the rest as follows:
Here are the results I got:
p.s. By default, the results are given in whole numbers. If you want to include decimals, make sure to update the visual to include decimals.
I hope this resolves the issue you're facing.
Thanks.
4 Replies
Anonymous
I think the easiest way would be to, simply, add a quick measure "Weighted Average per Category" . I tested with your sample data, and confirmed that it genrates the expected results. Hope it helps.
OR
Then, select "Weighted Average per category" from the dropdown, and fill out the rest as follows:
Here are the results I got:
p.s. By default, the results are given in whole numbers. If you want to include decimals, make sure to update the visual to include decimals.
I hope this resolves the issue you're facing.
Thanks.Break this up into indivual measures, makes it much eaiser...
SUM_Time = SUM('Table'[Time])DIST_Count_Tasks = DISTINCTCOUNT('Table'[Task])Measure = [SUM_Time] / [DIST_Count_Tasks]Anonymous ,
Try a measure
divide(sum(Table[Time]), distinctcount(Table[Task]))
or
Averagex(summarize(Table, Table[Month] , Table[Category],"_1",divide(sum(Table[Time]), distinctcount(Table[Task]))),[_1])
