Another 100% Stacked Bar Chart Question

[iamblue91]

Hi all! 

Really beginner question, so I apologize. 

I'm trying to sort Task Type as a share of % of an employee's total time for the month. The current issue is in the image attached -- each person just has a count of tasks and a sum of hours. Assume that Employee A spent 25% of their time on each Administration, Research, Writing, and Meetings. I want to show the 25% share of each. 

We use Harvest to track our time, which is downloaded to an Excel file. The way the data is exported is based on each person's time per day per task (based on project, but I'm not worried about that right now). I've provided an example of what this would look like. 

Date	Client	Project	Task	Hours	Billable	Name	Billable Amount	Cost Amount
	Tesla	Battery	Admin	4		Bob
	Tesla	Battery	Meetings	4		Joe
	Tesla	Battery	Writing	4		Joe
	Tesla	Battery	Research	4		Joe

I have a feeling that I have to implement some DAX....? (I'm VERY new to using PowerBI and DB query writing, though I am somewhat comfortable in Excel)

[dearwatson · Accepted solution]

Hi iamblue,

Welcome to the revolution 🙂

there are ways to do this without DAX but if you want to use PowerBI in any meaningful way you'll need DAX... and this is a good one to get started on:

First we make an easy measure: SUM of Hours (in this case the column Table1[Hours]) - this is already being done for you automatically in your graph but we create an "Explicit Measure" because we should NEVER use implicit measures according to the gurus of DAX.

create a measure:

Total Hours = SUM(Table1[Hours])

Now we use DAX magic to give us a measure that will always return the TOTAL hours even if we filter it...

We'll call it a Grand Total.

Grand Total Hours = CALCULATE([Total Hours],ALL(Table1)) -- this uses CALCULATE to remove the filters with the ALL function.

so the percentage calculation is:

Perc of Total Hours = DIVIDE([Total Hours], [Grand Total Hours],0)

Add the "perc of Total Hours Measure" as the Value in the 100% stack. Person Name in Axis, Activity in Legend... DONE.

Now that you learned the DAX way here's the 2 second no DAX solution:

=============================

Create blank 100% stacked bar graph

drag Hours to Value (<-- this defaults to an Implicit SUM so you really should create a measure)

drag Name to Axis

drag Activity to Legend

In the Visualizations pane click the little down arrow next to "Hours"

select Quick Calc->Show Value as Percent of Grand Total

Viola!

That should do it...

[iamblue91]

Thanks DearWatson! 

Glad to be part of it! Thanks for your detailed response -- as for DAX, I intend to learn at least some of the basics so I can grow a bit more as I need more in-depth analysis.

I appreaciate you providing both solutions! I'm learning this on my own, and it's good to know there's a community here that I can bounce my questions off of!

I'll try the solution tonight and report back!

[sebastian_ebg]

You don't actually need DAX, you just need a better understanding of how stacked charts work 🙂

Firstly, make sure that the default summarization option under the "Modeling" ribbon on the "Hours" field is set to "Sum" then delete the "Count of tasks" from the Values and finally drag the Tasks Hierarchy or the Task field into Legend. Done!

[iamblue91]

Thanks!