Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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.
Solved! Go to 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
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
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
@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
Hi !
Thanks for your answer.
- I used a Calendar table as you suggest
- I created this measure :
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |