how to show column total in stacked column chart where measure is a ratio

[Anonymous]

I want to show the total of the segments in a stacked column chart.  However, because my measure is a ratio, I don't think the usual solution pattern (https://radacad.com/showing-the-total-value-in-stacked-column-chart-in-power-bi) applies (at least, not without modification).  My report is about timesheet compliance for employees.  Below is a sample of the only table in my data model:

Employee Key	Employee Category 1	Employee Category 2	Day Key	Week Key	Hours Entered	Hours Expected
1	A	x	Monday	1	0	8
1	A	x	Tuesday	1	8	8
1	A	x	Wednesday	1	8	8
1	A	x	Thursday	1	0	8
1	A	x	Friday	1	8	8
2	B	y	Monday	1	8	8
2	B	y	Tuesday	1	8	8
2	B	y	Wednesday	1	0	8
2	B	y	Thursday	1	8	8
2	B	y	Friday	1	8	8

Timesheet compliance is defined as the number of hours entered as a percentage of the hours expected.  I want to show this on a stacked column chart, where x axis = [Week Key] and legend = [Day Key].  I want to show this at both the atomic employee level, their rollups (by [Employee Category 1] and [Employee Category 2]), and at the company level.  So, I have put [Employee Key], [Employee Category 1], and [Employee Category 2] as fields in the filter pane, and I have created 3 measures (not calculated columns, because the final measure is a ratio):

[Sum of Hours Entered] = SUM('Table'[Hours Entered])

[Sum of Hours Expected] = SUM('Table'[Hours Expected])

[Timesheet Compliance] = DIVIDE([Sum of Hours Entered], [Sum of Hours Expected], 0)

All of this works as desired.  However, in order to show the sum (total) of each of the column segments, the usual solution (convert to stacked column and line chart, use the same measure in both Column values and in Line values, i.e., [Timesheet Compliance]) will not work.  Using [Timesheet Compliance] for Line values does not return the sum of each column segment.  Instead, it returns an overall ratio.  How can I return the sum of each column segment, ensuring that this value is valid for any selection of the fields in my filter pane?

[v-kelly-msft]

Hi @Anonymous ,

Try to modify your measure as below:

Sum of Hours Expected = SUMX(FILTER(ALL('Table'),'Table'[Employee Category 1]=MAX('Table'[Employee Category 1])&&'Table'[Day Key]=MAX('Table'[Day Key])&&'Table'[Week Key]=MAX('Table'[Week Key])),'Table'[Hours Expected])

Sum of Hours Entered = SUMX(FILTER(ALL('Table'),'Table'[Employee Category 1]=MAX('Table'[Employee Category 1])&&'Table'[Day Key]=MAX('Table'[Day Key])&&'Table'[Week Key]=MAX('Table'[Week Key])),'Table'[Hours Entered])

[Timesheet Compliance] = DIVIDE([Sum of Hours Entered], [Sum of Hours Expected], 0)

If it doesnt work,pls advise me your expected output,and if possible,pls provide me your .pbix file.

Much appreciated.

Best Regards,
Kelly

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

[Anonymous]

@v-kelly-msft 

That solution did not work.  And, it made my column segments incorrect.  Here is what your solution produces:

Here is my data, my computations (for the "no filter selection" scenario), and my expected output:

[v-kelly-msft]

Hi @Anonymous ，

You need 2 calculated columns as below:

sum of hours entered = 
var preindex= CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Day Key]=EARLIER('Table'[Day Key])&&'Table'[Week key]=EARLIER('Table'[Week key])))
Return
IF('Table'[Index]=preindex,BLANK(),'Table'[Hour Entered]+SUMX(FILTER('Table','Table'[Index]=preindex),'Table'[Hour Entered]))

sum of hours expected = 
var preindex= CALCULATE(MAX('Table'[Index]),FILTER('Table','Table'[Day Key]=EARLIER('Table'[Day Key])&&'Table'[Week key]=EARLIER('Table'[Week key])))
Return
IF('Table'[Index]=preindex,BLANK(),'Table'[Hour Expected]+SUMX(FILTER('Table','Table'[Index]=preindex),'Table'[Hour Expected]))

And 2 measures as below:

Timesheet = DIVIDE(MAX('Table'[sum of hours entered]),MAX('Table'[sum of hours expected]))

Total Timesheet = SUMX(FILTER(ALL('Table'),'Table'[Week key]=MAX('Table'[Week key])),'Table'[Timesheet])

If you wanna show total in the stacked column chart,you'd better change the visual to "Line and stacked column chart",and under "format">"shape",choose "stroke width" to "0",shown as below:

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[Anonymous]

@v-kelly-msft 
Is there any way to modify this solution so that the visuals reflect filter pane selections?  Even though this solution works at the company level, I need it to also work at any given employee rollup level (e.g., [Employee Category 1] or [Employee Category 2]) and the employee level (i.e., [Employee Key]).  That is why I have fields [Employee Category 1], [Employee Category 2], and [Employee Key] in my filter pane.  When I select anything about employee #1, there is no change to the visuals.  When I select anything about employee #2, the stacked column + line chart disappears, and all fields in the table visual except for [Week key], [Date Key], and [Total Timesheet] turn blank.  To be sure, if it matters, in my actual pbix file, each [Employee Category 1] and [Employee Category 2] value will appear on multiple employees (e.g., employee #3 could have combination A-y, and employee #4 could have combination B-x).

[Anonymous]

@v-kelly-msft 

Any ideas on how to make this solution also work to reflect filter context (i.e., filter pane selections)?

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

I have modified the column to below measures:

_sum of hours entered = 
SUMX(FILTER(ALL('Table'),'Table'[Week key]=MAX('Table'[Week key])&&'Table'[Day Key]=MAX('Table'[Day Key])),'Table'[Hour Entered])

_sum of hours expected = 
SUMX(FILTER(ALL('Table'),'Table'[Week key]=MAX('Table'[Week key])&&'Table'[Day Key]=MAX('Table'[Day Key])),'Table'[Hour Expected])

_Timesheet = DIVIDE([_sum of hours entered],'Table'[_sum of hours expected])

_Total timesheet = SUMX(FILTER(ALL('Table'),'Table'[Week key]=MAX('Table'[Week key])&&'Table'[Employee Key]=MAX('Table'[Employee Key])),[_Timesheet])

Then you can use the filters as you hope.

Here is the modified .pbix file.

Best Regards,
Kelly

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

[v-kelly-msft]

Hi @Anonymous ,

Can you upload your .pbix file to onedrive business and share the link with me?Better attach your expected output.

Best Regards,
Kelly

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

[Anonymous]

I can't share my pbix file with you, as my company has disabled all external sharing in our tenant.