% of Grand Total of a Measure that use other Measures

[MarkDGaal]

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

I have a Matrix 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.

[Total Cost] is a Measure which adds together 5 other Measures from 5 different tables

[Hours] is a Measure which adds together a column from a table and divides by the distinct count of records in that table

[Cost per Hour] is a Measure = [Total Cost] / [Hours]

All of these Measures are filtered by multiple filtered visualizations and page level filters from many different tables and have a relationships created using an index number between the tables.

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.

[v-caliao-msft]

Hi @MarkDGaal,

In your scenario, does the total row is auto generaled? If that is the case, then why can you get 12.495 for Cost per Hour column. Generally, the total show 140 for Cost per Hour column.

And if you create calculated columns instead of measures, you can get your expected result by using the DAX below.
Percentage = Percentage[CostPerHour]/SUM(Percentage[CostPerHour])

Regards,

Charlie Liao

[MarkDGaal]

Following up, I mentioned in my original post that there exists several filter visualizations against the measures created and those filters exist in different cost tables than the table I'm using to create my table visualization. These cost tables are linked to the table used in the table visualization by an index number and a many-to-one single direction filter relationship. Is there any easy way for my cost per hour column you are suggesting to be created in the table used in the table visualization to be filtered by columns/filters that exist in other tables and are only linked by index number.

For example, if I were to create the numerator [total cost] of the [costperhour] calculation I would do the following. [total Cost] = sum(table2[value])+sum(table3[value])+sum(table4[value])+sum(table5[value])+etc....

Table1 where the [total cost] column is being created is used in the table visualization
Tables 2-5 are linked to table1 using a many-to-one single direction filter relationship and an index number
Filter visualizations are created from columns in tables 1 & 2 & 5

How do I make my calculated columns apply all the filter visualizations created using several different tables?

[MarkDGaal]

@v-caliao-msft hi there, the total row is the default "totals" option for the standard Table Visualization. I'm not sure how I can get the totals row to evaluate to 12.1495 for [Cost per hour] but I assure you it does....

If you'd like to explore deeper here are the the measures that lead to the totals row evaluating to 12.1495 for [CostperHour].

Hours = IF(ISFILTERED('Tabe1'[FY]),SUM('Table1'[Hours])/DISTINCTCOUNT('Table1'[Column Header Name]),0)

Total Cost = [cost1]+[cost2]

CostPerHour= IFERROR(([cost1]+[cost2])/[Hours],0)

cost1 = IF(ISFILTERED(table2[AGB Cost Element]) || ISFILTERED(table2[Cost Category])|| ISFILTERED(table2[Flying or Non-Flying])|| ISFILTERED(table2[Org Level]) || ISFILTERED(table2[Personnel Category]), SUM(table2[Value]),0)

cost2 = IF(ISFILTERED('table3'[AGB Cost Element]) || ISFILTERED('table3'[Cost Category]), SUM(table3[Fuel Cost]),0)

If I recall correctly calculated columns have trouble using measures so I will recreate all of my measures as calculated columns and let you know if that solves my problem tomorrow.