Forum Discussion
Graph with sum line
Good morning!
I need a help, I'm developing a report where the graph showed me the total of admitted per month,
I would like to know how to do it, add up the values from the previous months, to bring me a line that demonstrates the total of employees of the company.
Thank you very much in advance,
Att,
This is a classic problem that I have seen a few times, the solution is to create an "active employees" measure with a disconnected Calendar.
To do this you will need a Calendar table with contigeous days in it.
The fastest way to do this is create a new table
Calendar = CALENDARAUTO()... this will scan your data and build table with a list of dates. I recommend you build a proper date dimension but this does the trick.
This Calendar[Date] is used to slice the data, no relationships are needed as we build a measure to lookup the value.
Then your employee count measure:
Employees = DISTINCTCOUNT(Table1[EmployeeID])
Then you need a measure that counts the employees whose "admission date" is before (<=) the last date in the selected period and "termination date" is after the first date in the selected date period
CALCULATE([Employees], FILTER(Table1, (Table1[Admission Date] <= LASTDATE (Calendar[Date])) && (Table1[DismissalDate] >= FIRSTDATE(Calendar[Date]))))
7 Replies
Hi joubertsaquett
I think you just need to use ALL to remove the filters and show the total.
Measures:
Admissions = SUM(Table1[Admissions])
All admissions = CALCULATE([Admissions],ALL(Table1))
This will always return the total admissions from all data/time
Thank you, I'll take the test!
You can create a measure like:
Measure = CALCULATE(SUM([Value]),ALL(Table))
Then just add this as a Value in your chart.
Thank you for your help,
One more doubt is it possible to perform some calculation (admissions / layoffs) to show the growth of the company in the chart?
That would depend on your data and the format of that data. But, yes, if you have a list of people who have left, then you should be able to perform that calculation. Can you show some example data?
