% of Grand Total of a Measure that uses other Measures and is Crossfiltered

Question

this seems so easy in my head but I haven't been able to get it for the last few hours....

I have a Table visualization that provides Cost by Hour using measures.

Category | Total Cost | Hours  | Cost per Hour
A        | 1000       | 10     |  100
B        | 2000       | 100    |   20
C        | 100        | 4      |   25  
D        | -500       | 100    |  -5
Total    | 2600       | 214    | 12.1495

For my purposes, I would also like to create a % of Grand Total of Cost per hour to add to a treechart visualization. However, if I simply add [Cost per Hour] to the treechart again and use the "quick clac" functionality on the field it would return 823.7% for the first record in the above table as (100/12.1495) = 8.2307. I would like this % of GT of Cost per Hour to use the total sum of the Cost per Hour column. Desired Result:

Category | Total Cost | Hours  | Cost per Hour | % of Cost per Hour
A        | 1000       | 10     |  100          | 71.4%
B        | 2000       | 100    |   20          | 14.3%
C        | 100        | 4      |   25          | 17.9%
D        | -500       | 100    |  -5           | -3.8%
Total    | 2600       | 214    | 12.1495       | 100%

A few things to note that makes the application of any DAX challenging. All of the below Measures are filtered by multiple filter visualizations from Tables 1-5 and page level filters from Tables 1-5

The table visualization exists in Table1. Costs exist in Tables 2-5 and are related to Table1 using a Many-to-One Single Direction Filter Relationship.

[Total Cost] is a Measure that adds together values from 4 different tables. Eg:Total Cost = sum(table2[value])+sum(table3[value])+sum(table4[value])+sum(table5[value])

[Hours] is a Measure that adds together a column from a table and divides by the distinct count of records in that table. Eg:Hours = sum(table1[hours])/Distinctcount(table1[records])

[Cost per Hour] is a Measure consisting of two other measure.Cost per Hour = [Total Cost] / [Hours]

I sort of feel like this is similar to people wanting to add percentages to pie charts... I'm just trying to ascribe a real number to express the proportion displayed in the TreeChart visualization. I really hope that this is easier than it seems.

EDIT @alejandrozuleta: Table1 is the original table from which tables 2-5 are referenced&created. An index number was assigned in Table1 and tables 2-5 are linked on this reference number. The reason that tables 2-5 exists separately is because they contain separate cost "types" and a join that occurs in these tables adds additional columns that are only applicable to specific costs types.... for example Table2 is Personnel Costs:

index | Category | Cost Type |  Value | Age of Personnel
1     | A        | Personnel | 1      | 33

and Table3 is Maintenance Costs:

index | Category | Cost Type   |  Value | Scheduled or UnScheduled Maint
2     | A        | Maintenance | 5      | Scheduled

The if [Age of Personnel] existed in Table3 then it would have a "null" for any record of the Maintenance [Cost Type] vice-versa [Scheduled or UnScheduled Maint] would have a "null" if it existed in Table2. Because I don't want to have to deal with filter visualizations needing to select "(blanks)" for certain costs types the data relationship between these tables is a Many-to-One Single Direction Filter using [index] as the key.

EDIT2: Working .pbix file with notional data and the data model I described is linked: StackOverflow_GTofMeasure_Crosfilltered.pbix

Could you add tables 2,3,4,5 sample data and exaplain better how these are related to table 1. — alejandro zuleta

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-09-23T19:43:40

I think this solution could work for you. Basically I've created two helper measures (which you don't have to show in your table):

CostPerHourHelper = SUMX(TableName,[Cost per Hour])

CostPerHourTotal = SUMX(ALL(TableName),[Cost per Hour])

Now you can create your % of Cost per Hour measure using this expression:

% Cost Per Hour = [CostPerHourHelper]/[CostPerHourTotal]

It should produce:

UPDATE:

Use ALLSELECTED() function to preserve the explicit filters you applied.

% Cost Per Hour = SUMX ( TableName, [Cost per Hour] )
    / SUMX ( ALLSELECTED ( TableName ), [Cost per Hour] )

Let me know if this helps.

% of Grand Total of a Measure that uses other Measures and is Crossfiltered

2 Answers