Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
m051896
New Member

Stacked Bar Chart by Month where the Monthly Total Equals the Sum of a Column

Hello,

 

I've gotten a lot of tips from this forum in the past, but never posted a question.  I can't find a good answer for something I'm trying to do, so I'm hoping someone here can help.

 

My end goal is to produce a stacked bar chart, where the total value of the bar chart is the total possible bookable hours for everyone on my team in each month.  The individual elements that add up to the total are: Absorbed Hours and Unabsorbed hours.  My company used to have all of this information in a single system, but recently we split it into two different systems - only one of which I have access to.  I am having to create a workaround.  Here's what my old BI chart used to look like:

m051896_2-1644533403842.png

 

Now, I have two tables of data.  One table (the Absorbed Hours) shows all time booked to a paying customer account.  This part is the same as it's always been - although this system used to show the Unabsorbed Hours as well.  The table is set up so that each row shows the number of hours an employee booked on a given day.  It's called "HoursTable" and looks something like this:

m051896_3-1644534461178.png

 

Since I can't access our other system, I built another table that shows the total possible time employees could have booked in a given month, taking into account holidays and weekends.  It's called "BasicTable" and looks something like this:

m051896_1-1644533168063.png

 

I also have a dates table (along with several other tables unrelated to this problem).

 

I already have a measure to calculate the total Absorbed Hours by month:

(AbsorbedHours = calculate(sum(HoursTable[Hours]),filter(HoursTable,HoursTable[Receiver cost center]= BLANK()))

This works (the Receiver Cost Center is blank for all Absorbed hours...it's an artifact of our old system).

 

What I'm trying to do is calculate the total possible hours, minus the total Absorbed Hours, and come up with the Unabsorbed Hours by month to stack my bar chart.  Ideally it would also be filterable by individual employee, as it used to be.

 

It seems that no matter what I do, my Unabsorbed Hours measure either gives me the total possible hours for January minus Absorbed Hours, repeated for each month (i.e., it uses January's total for Feb, Mar, etc., instead of taking the total for those months); or I get the sum of possible hours for all 12 months, minus the Absorbed Hours by month.

 

Any thoughts?

 

Thanks.

 

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@m051896  You need to unpivot your Basic Table so that it has three colunms:

 

Person ID | Month | Hours

 

The month should be the first of the month so you can relate it to the date table.

Relate the Person ID to the dimEmployee table.

 

Then create a new measure for Total possible hours: 

Total Available Hours = SUM( BasicTable[Hours] )

 

Then subtract your two measures and put in a visual.

 

Note: If every employee has the same number of total available hours you can get rid of your basic table entirely and just calculate this from the Date table, but whatever gets your model working for you is the right solution. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

2 REPLIES 2
m051896
New Member

Thanks Alisson, that did it. 

AllisonKennedy
Super User
Super User

@m051896  You need to unpivot your Basic Table so that it has three colunms:

 

Person ID | Month | Hours

 

The month should be the first of the month so you can relate it to the date table.

Relate the Person ID to the dimEmployee table.

 

Then create a new measure for Total possible hours: 

Total Available Hours = SUM( BasicTable[Hours] )

 

Then subtract your two measures and put in a visual.

 

Note: If every employee has the same number of total available hours you can get rid of your basic table entirely and just calculate this from the Date table, but whatever gets your model working for you is the right solution. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.