cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joubertsaquett Frequent Visitor
Frequent Visitor

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,

Screenshot_3.png

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,

1 ACCEPTED SOLUTION

Accepted Solutions
dearwatson Member
Member

Re: Graph with sum line

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]))))

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
7 REPLIES 7
dearwatson Member
Member

Re: Graph with sum line

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 

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Super User
Super User

Re: Graph with sum line

You can create a measure like:

 

Measure = CALCULATE(SUM([Value]),ALL(Table))

Then just add this as a Value in your chart.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


joubertsaquett Frequent Visitor
Frequent Visitor

Re: Graph with sum line

Thank you, I'll take the test!

joubertsaquett Frequent Visitor
Frequent Visitor

Re: Graph with sum line

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?

Super User
Super User

Re: Graph with sum line

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


joubertsaquett Frequent Visitor
Frequent Visitor

Re: Graph with sum line

Yes I can,
In this image below, inside the "square" are the fields of the database.
Screenshot_4.png
1. date of admission (ex: 20170130)
2. Date of dismissal (ex: 20170130)
3. Payroll situation (D = Dismissed or EMPTY = active)

The other fields are "measures"

 

In this image, it correctly displays the total number of employees I have active today, but it does not change according to the date.

Screenshot_5.png

obs. We created measure for dates of admission and dismissal by changing the "field format" to date

 

Screenshot_7.pngScreenshot_6.png

 

If you need more information please let me know,
Thank you for the great help,

 

dearwatson Member
Member

Re: Graph with sum line

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]))))

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte