Showing percentage values in Stacked Column Chart

[Anonymous]

Hello to all

I'm trying to display the % for each Activity in the graphic displayed below. 

In my case I have the days work in one table and the list of activities in other. They are linked by an id. 

In this case I wanna display 28,81/40 %. I'm trying to use a measure but the value is not correct.

What am I missing?

Days worked are the Value

Activities are the Legend.

I do no have any calculation for each activity.

Thanks

[MFelix]

Hi @Anonymous,

Use the 100% Stacked Column Char that will give you the percentsge and add days work in the tips to show.both when.you are over the.values.

Regards,
MFelix

[Anonymous]

Hello

Thanks @MFelix but in fact it is more important to show the other way around. I tried your approach but it was not very good for our dashboard viewers. 🙂

[MFelix]

HI @Anonymous,

You can add the 40% in the tooltip not in the data labels. Is this value a measure or direct calculation in your table?

MFelix

[Anonymous]

In fact is a column.

I managed to make it appear in the tool tip by using a %GT. But If I had more weeks, it calculates to all those weeks (expectable) but it was not what I want. I want the % by each week and activity.

Maybe I have to change something in the data. But not figuring out what.

The days worked are just a simple conversion of seconds to days, there are several entries for each day/week by person.

There is a relationishp beetween the days and the sub-activity which is in another table that has the main activity.. Maybe I have to do a measure to add the time by each sub-activity, and use that. But I tried that approach and failed.

[MFelix]

Can you share data to make some tests?

[Anonymous]

See if the image helps:

Above we have the table that contains the time logged by user and the sub-activity. This sub-activity is under a project (or Activity)

in the visual I Have:

Week in Axis

Legend Activities

Value the days worked

Tooltip %GT Days Worked

[MFelix]

Hi @Anonymous,

I have made a simplified model of you data in PBI working with the columns you needed to your chart I have come up to the result below.

Create the following measure in the Time_Log table:

Working days % per week = 
	Var Total_Week_Days = CALCULATE 
					( SUM( Time_Log[DaysWorked] ); 
 						FILTER (               
							 ALL ( Time_Log );
							 Time_Log[Week] <= MAX ( Time_Log[Week] )
							 );
 						VALUES ( Time_Log[Week] )
					)
         return
	     SUM(Time_Log[DaysWorked])/Total_Week_Days

The variable in the measure returns the total number of days worked and that's what I use to calculate the percentage per week. In the print screen you can check the result for comparason of the measure above and the Grand Total I first told you.

As you can see the first matrix (red highlight) as a total of 100% in the table, the second one using the measure (green highligth) as a total of 100% per week (column) that is what I believe you want. 

 

If you want the total number of days use only the var Total_Week_Days as your measure.

Please also check the table names.

Regards,

MFelix

[Anonymous]

@MFelixthanks for the quick reply.

I used your measure but it gives me a very low numbers like 0,01.

I think i'm doing something wrong.

Maybe is adding values all instead of values per week.

[MFelix]

Forgot to mention you need to format the measure as a %.

Sorry.

MFelix

[Anonymous]

@MFelix, Just did that but I get something like 1.96%, 1.8% 😞

If I use just the "calculate" part I get something 1279766. which is very high, but in your sample is correct. 😞

[MFelix]

Can you please share the measure you calculated?

Mfelix

[Anonymous]

Working days % per week = 
	Var Total_Week_Days = CALCULATE 
					( SUM( worklog[Days Worked] ); 
 						FILTER (               
							 ALL ( worklog );
							 worklog[Week] <= MAX ( worklog[Week] )
							 );
 						VALUES ( worklog[Week] )
					)
         return
	     SUM(worklog[Days Worked])/Total_Week_Days

This is what I did.

In fact after seen you reply I got back to the calculation and saw that I was using a column and not a measure so now I see the correct value in the "Calculate" part.

Please note that:

Days Worked = worklog[timeworked]/3600/8 to convert to "days of work".

[MFelix]

Sorry but your message was dual, were you able to  make it work as a measure?

MFelix

[Anonymous]

@MFelixsorry for the confusion.

If I put the Calculate part of you measure formula I get the value that you can see in the image:

Anf that value is not correct, maybe because I'm filtering by another entry, it seems the measure shows the total value? I'm filtering by team using a correlation between author and the team. In the data I showed earlier all people info go to that table.

[MFelix · Accepted solution]

Hi @Anonymous,

If I understand corrcly you are filtering your worklog by a slicer/filter try to adapt your formula to this

Working days % per week = 
	Var Total_Week_Days = CALCULATE 
					( SUM( worklog[Days Worked] ); 
 						FILTER (               
							 ALLSELECTED ( worklog );
							 worklog[Week] <= MAX ( worklog[Week] )
							 );
 						VALUES ( worklog[Week] )
					)
         return
	     SUM(worklog[Days Worked])/Total_Week_Days

 Instead of ALL I use ALLSELECTED that refers to the table filter/sliced.

Regards,

MFelix

[Anonymous]

@MFelixPerfect!! I manage to do it by using an ALLEXCEPT, but your version is cleaner and more flexivel.

Many Thanks

[MFelix]

Great news 

Please mark the answer so other can be helped.

Regards,

MFelix