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

View solution in original post

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.


I have book! Learn Power BI from Packt


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?


I have book! Learn Power BI from Packt


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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 399 members 3,637 guests
Please welcome our newest community members: