Monthly trend after filtering by annual total

[Rudy_D]

Hi guys,

I have some issues trying to solve the problem below.

I've got one table 'hour' listing overtime hours per personne ID / month and year. 

ID	Month	Year	Total Overtime Hour
501	1	2021	50
501	2	2021	51
503	1	2021	25
503	2	2021	78
504	1	2021	10
505	1	2021	10
506	1	2021	10
507	1	2022	11

After filtering one particular Year through a slicer, I would like to :

1) Get a list of all the ID for which the annual total of overtime hours > 100

2) Get a graph of monthly total overtime hours ONLY for the scope detailed above (ID for which the annual total of overtime hours > 100)

For the 1), I've sold the problem by creating the measure above and using it as a filter on a table visual :

COUNTROWS(

FILTER(SUMMARIZE(hour, hour[ID], hour[year], "sum", sum(hour[Total Overtime Hour])), [sum] > 100))

For the 2), I don't know how to do it :-/.

This is what I expect for 2021 : as only ID 501 et ID 503 exceed 100 annual overtime hours, so I will have this graph :

Thank you very much for your help.

[v-yangliu-msft · Accepted solution]

Hi  @Rudy_D ,

Here are the steps you can follow：

Question 1:

1. Create measure.

Sum_selectyear =
var _year=SELECTEDVALUE('Table'[Year])
var _flag=CALCULATE(SUM('Table'[Total Overtime Hour]),FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])&&'Table'[Year]=_year))
return
IF(
    _flag>100,1,0)

2. Place [Sum_selectyear] in Filters, set is=1, apply filter.

3. Result:

Question 2：

1. Create calculated table.

Table 2 =
var _1=SUMMARIZE('Table','Table'[ID],'Table'[Month],'Table'[Year],'Table'[Total Overtime Hour],"1",CALCULATE(SUM('Table'[Total Overtime Hour]),FILTER(ALL('Table'),[ID]=EARLIER([ID])&&[Year]=EARLIER([Year]))))
var _2=
FILTER(_1,[1]>100)
return
_2

2. Result.

There are only data from January to February in the sample data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Rudy_D]

Hi Liu,

Thanks for your help.

I had some trouble with both : 

MAX('Table'[ID])

EARLIER('Table'[ID])

So instead of these filter parameters, I used the expression below as table to filter for both measure and calculatedtable you suggested and it works fine :

ALLEXCEPT(Table, Table[ID])

Thank you very much !

Rudy

[amitchandak]

@Rudy_D , You can get full year data using date table and time intelligence like

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

or

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

You can use that as table level filter

else

you need use grouping in visual to filter 

sumx(values('Date'[Month]), [This Year] >0 ), [Measure] ) 

//Measure you want display on visual , use measure or use calculate on aggregated columns

[Rudy_D]

Hi !

Thanks for your answer.

- I used a Calendar table as you suggest
- I created this measure  : 

This year = CALCULATE(sum(hour[Total Overtime Hour])), filter(ALL('Date'), 'Date'[IsoYear] = 2021 ))

Using this measure as a filter is an alternative of my solution for solving the question 1 (got the same result per ID)

But I don't understand how to use it to answer question 2.

Thanks for your help.

Rudy