Need help for total by month and year

[sam021083]

Hi All,

I am struggling to find a solution for the below scenario. As shown below I have the year, quarter and month columns and for each month I have (Low, Medium, High, Undetermined) indicators and the corresponding tasks. I would like to achieve the result of the 5th and 6th columns (I would like to create measures for this). For each year, I like to calculate the distinct number of months per year (which have tasks assigned). And the 6th column would be the total number of tasks for that year, And the last column will be the result of "Sum of tasks yearly/Desired Measure(For Months)"

Year	Quarter	Month	Indicator	Tasks	Desired Measure(For Months)	Sum of tasks yearly	Average
2018	Qtr 4	October	Low	1	2	3	1.5
2018	Qtr 4	October	High	1	2	3	1.5
2018	Qtr 4	November	Low	1	2	3	1.5
2019	Qtr 1	January	Low	5	3	28	9.333333
2019	Qtr 1	January	Medium	1	3	28	9.333333
2019	Qtr 1	January	High	1	3	28	9.333333
2019	Qtr 1	January	Undetermined	2	3	28	9.333333
2019	Qtr 1	February	Low	2	3	28	9.333333
2019	Qtr 1	February	Medium	1	3	28	9.333333
2019	Qtr 1	February	High	2	3	28	9.333333
2019	Qtr 1	March	Low	5	3	28	9.333333
2019	Qtr 1	March	Medium	2	3	28	9.333333
2019	Qtr 1	March	High	6	3	28	9.333333
2019	Qtr 1	March	Undetermined	1	3	28	9.333333

Any help much appreciated!

Thanks,

Sam

[sam021083 · Accepted solution]

After going through other help topics, I was able to find the solution. 

The below measure will provide the correct results. However, if we have any blank values in any of the columns this will formulae doesn't work and we need to add a filter condition in place 'ALL'.

Measure = CALCULATE(DISTINCTCOUNT(Table1[Month]),ALL(Table1),VALUES(Table1[Year]))

Thanks,

Sam

[parry2k]

@sam021083 add following 3 measures

Distinct Month Count = DISTINCTCOUNT( Table[Month] )

Tasks = SUM( Table[Tasks] )

Avg = DIVIDE( [Tasks], [Distinct Month Count] )

Drop a table visual and Year, and 3 new measures on values.