Dynamic percentage based on attributes applied

[Anonymous]

I'd like to create a measure that displays a percentage of time-phased labour cost data dependent on the attributes added to a visual. The attributes I want to be able to apply are (I want the ability to apply any number of these attributes to see, for example, things like the percentage of time/cost a given team has spent on a project):

- Project Name

- Resource Name

- Resource Role

- Resource Team Name

- Month & Year

I grouped the data in the table via the Query Editor and added a Total Cost column so the Actual Cost value can be divided by Total Cost to display a percentage, but am stuck on the best measure to create for this. 

Here is a sample of the type of data I'm working with and how it's been grouped:

ResourceName	ProjectName	TeamName	Role	Month & Year	Start of Month	SumActualCost	Total Cost
Resource I	Example Project	Team T	Role O	Jan-2018	1/01/2018	5165	137480.1
Resource A	Example Project	Team T	Role O	Nov-2017	1/11/2017	5162.5	137480.1
Resource BB	Example Project	Team C	Role N	Jun-2017	1/06/2017	5075	137480.1
Resource V	Example Project	Team T	Role L	Dec-2017	1/12/2017	5075	137480.1
Resource V	Example Project	Team T	Role L	Dec-2016	1/12/2016	5025	137480.1
Resource V	Example Project	Team T	Role L	May-2016	1/05/2016	4975	137480.1
Resource F	Example Project	Team T	Role M	Dec-2016	1/12/2016	4975	137480.1
Resource V	Example Project	Team T	Role L	Sep-2016	1/09/2016	4950	137480.1
Resource I	Example Project	Team T	Role O	Apr-2017	1/04/2017	4915	137480.1
Resource O	Example Project	Team D	Role D	Aug-2016	1/08/2016	4875	137480.1
Resource A	Example Project	Team T	Role O	Sep-2017	1/09/2017	4837.5	137480.1
Resource I	Example Project	Team T	Role O	Feb-2016	1/02/2016	4835	137480.1
Cost Resource B	Example Project		Role C	Jul-2017	1/07/2017	4827.611483	137480.1
Resource C	Example Project	Team M	Role O	Jun-2017	1/06/2017	4825	137480.1
Resource F	Example Project	Team T	Role M	Jan-2018	1/01/2018	4800	137480.1
Resource A	Example Project	Team T	Role O	Jun-2017	1/06/2017	4762.5	137480.1
Resource V	Example Project	Team T	Role L	Jul-2016	1/07/2016	4725	137480.1
Resource F	Example Project	Team T	Role M	Dec-2017	1/12/2017	4725	137480.1
Resource F	Example Project	Team T	Role M	Aug-2017	1/08/2017	4700	137480.1
Resource R	Example Project		Role F	Apr-2016	1/04/2016	4625	137480.1
Resource F	Example Project	Team T	Role M	Jan-2017	1/01/2017	4500	137480.1
Resource BB	Example Project	Team C	Role N	Aug-2017	1/08/2017	4450	137480.1
Resource E	Example Project	Team T	Role M	Mar-2016	1/03/2016	4425	137480.1
Resource S	Example Project	Team A	Role K	Oct-2016	1/10/2016	4400	137480.1
Resource DD	Example Project	Team T	Role A	Sep-2017	1/09/2017	4400	137480.1
Resource E	Example Project	Team T	Role M	Feb-2018	1/02/2018	4400	137480.1
Resource A	Example Project	Team T	Role O	Mar-2017	1/03/2017	4375	137480.1
Resource F	Example Project	Team T	Role M	Apr-2017	1/04/2017	4350	137480.1
Resource V	Example Project	Team T	Role L	Mar-2016	1/03/2016	4325	137480.1

[Anonymous · Accepted solution]

Ok great, the formatting must have been the elusive element.

Doing some quick maths:

208764.42 / 37968039.17 = 0.005498425.  As a percentage that should be 0.54%.  So that means you are correct in that its 100 times too small, plus the number itself is wrong.

Lets create 2 more measures. 

Numerator = SUM('YourTable'[SumActualCost])
Denominator = SUM('YourTable'[Total Cost])

Bring those into the matix next.  I'm curious what that comes up with.

For the reason the reason it doesn't add to 100%, i believe My error is with Total Cost.  Instead of SUM for the Denominator, we should use MAX instead.  This is because you don't actually want to sum up that column.

[Anonymous]

Would a simple measure such as this do the trick:

Simple % = DIVIDE(
     SUM('YourTable'[SumActualCost]),
     SUM('YourTable'[Total Cost])
)

[Anonymous]

The results returned are all zero

[Anonymous]

Where are you putting the measure to be displayed?  What filter contexts are used?  Getting zero in this manner makes me suspect there is another element in play here.

[Anonymous]

The measure is created within this table. Which was originally two tables, but merged together in the Query Editor and then I used the Group By function and specified these columns, and aggregated the Actual Cost. No filters have been applied. The visual I'm viewing the result in is a table, and I had to specify the Total Cost column to "Don't Summarize".

[Anonymous]

Just to clarify,  The matrix visual in your screen shot has each item entirely from this single grouped table?

What does it look like if you bring the Measure onto your table, so you get a row by row result?

Is the table used for the matrix visual the same table referenced by the measure?

[Anonymous]

I've just changed the Format of the Measure to "Percentage" and now I'm seeing numbers but the total is 0.0087184% (shouldn't this be 100%?) and the row values aren't correct (see screenshot for example). I've tried multiplying the measure by 100 and this seems closer to what it should be, but as you can see below, a percentage for 2.3m is lower than 350k...?

[Anonymous · Accepted solution]

Ok great, the formatting must have been the elusive element.

Doing some quick maths:

208764.42 / 37968039.17 = 0.005498425.  As a percentage that should be 0.54%.  So that means you are correct in that its 100 times too small, plus the number itself is wrong.

Lets create 2 more measures. 

Numerator = SUM('YourTable'[SumActualCost])
Denominator = SUM('YourTable'[Total Cost])

Bring those into the matix next.  I'm curious what that comes up with.

For the reason the reason it doesn't add to 100%, i believe My error is with Total Cost.  Instead of SUM for the Denominator, we should use MAX instead.  This is because you don't actually want to sum up that column.

[Anonymous]

I've created those measures, do you want me to replace them in the % measures now? I can't seem to select measures in the formula.

The measures now return the same results as follows:

SumActualCost = Numerator

Total Cost (when values aren't aggregated) = Denominator

[Anonymous]

Excellent.  Try changing your % measure to use the numerator and denominator.  Curious at the result.

[Anonymous]

When I try to, I get an error saying the column can't be found. Should I make the Numerator and Denominator columns instead?

[Anonymous · Accepted solution]

No definately not, thats not necessary.  Does your formula now look like?

Simple % = DIVIDE(
     [Numerator],
     [Denominator]
)

Remembering Numerator and Denominator are measures, so we can call them directly in our new Measure.

[Anonymous]

Hi @Anonymous

The feedback I've received on this percentage has included a request for it to dynamically change the total value it's using to calculate the percentage based on the slicer selection. Is this possible? 

For example, if a specific project has been selected in the slicer, we would want the percentage calculated based on the total cost of that project, showing a percentage split across teams and roles. 

Thanks.

[Anonymous]

 @Anonymous  Have you tested it already with a slicer?  The formula is context driven, so it should correctly respond to any slicers you use.

[Anonymous]

Yes, applying the slicer currently doesn't change the total value it's calculating from. The percentage should total to 100% on the visual if that was the case.

[Anonymous]

This would likely be an issue with your data and your data relations.

All our measure do is sum up the individual rows, for both 'ActualCost' and 'TotalCost'.  The slicers will be adding a context to limit what is contained in both of those summations.

As to why it doesn't add to 100%, this will be entirely dependant on how your TotalCost column has had it data populated.  When selecting a subset of the data, yes you will be adding up less TotalCost lines, but i'm unable to say whether that change in context causes some invalidation of your information.

[Anonymous]

Hi

I created a new file with only the main table, without any transformations made to it. I added the measures and the same thing still happens. When a slicer is applied, the percentages don't recalculate to show the ratio within that selection. It appears to be hard-coded to the overall total cost rather than being dynamic. Is there a change I could try to the measures formulae?

Thanks

[Anonymous]

The slicers you are using, are they from the same table as the data or are the slicers referencing another table?  Is the table relationships correct to enable your slicers to work?

[Anonymous]

In the original file (with multiple tables and transformations) the slicer is from the same table as the measures. In the new file there is only one table and no transformations - so the slicer is from the same table as the measures again. 

The formula used for Total Cost is:

Original file (where the table has been grouped): Total Cost = SUM('AssignmentTPDS'[SumActualCost])

New file (where the table hasn't been grouped): Total Cost = SUM('Query1'[AssignmentActualCost])

[Anonymous]

@Anonymous - have you had anymore thoughts on this?

[Anonymous]

Sorry @Anonymous no I haven't.  As described, it should work.  Based on everything you have put forward, it should work.  There must be another unseen element at play.