SUM Ignoring Slicers

[Budfudder]

My visual has a simple data table which has a number of rows, each representing an invoice. Each invoice has a number of items, including the sales rep, creation date, and the amount.

I have a number of slicers including sales rep, date of creation, etc.

I'm trying to add a column which, for each row, shows the percentage that that particular invoice is of the shown invoices. So, for example, when I use a filter to only show a subset of all rows, I want each row to show a percentage of the total of that subset, not the percentage of all rows.

Imagine this table:

|ID|Rep |Value|Percentage|
| 1|Dave|   10|        50|
| 2|Sue |    3|        15|
| 3|Dave|    5|        25|
| 4|Sue |    2|        10|

The Percentage column is created by Value/SUM(Value) and is showing correctly above. The SUM(Value) is 20; Dave's 10 is 50% of that, Sue's 3 is 15% of it, and so forth.

I have a slicer for Rep. So if I choose 'Dave' in that slicer, the table looks like this:

|ID|Rep |Value|Percentage|
| 1|Dave|   10|        50|
| 3|Dave|    5|        25|

The Percentage column is being created based on the SUM(Value) of ALL rows (i.e. 20) not just the ones that are showing because of my slicer (i.e. 15). I would have expected the percentages to be 66.67% and 33.33%, being the percentages of the total of what's showing. Why isn't it? And how do I get it to be?

[jdbuchanan71 · Accepted solution]

Hello @Budfudder

It's the difference between ALL() and ALLSELECTED()

I have the following measures in the view below 

Values = SUM( Table1[Value] )

Values ALL = CALCULATE( [Values] , ALL ( Table1 ) )
Values ALL Selected = CALCULATE ( [Values] , ALLSELECTED ( Table1 ) )

% of total ALL = DIVIDE( [Values] , [Values ALL] , 0 )
% of total ALL Selected = DIVIDE( [Values] , [Values ALL Selected] , 0 )

With no slicer selection the amounts from [Values ALL] and [Values ALL Selected] are the same.

If I apply a filter on Dave [Values ALL] ignores that filter while [Values ALL Selected] respects it.

[Anonymous]

@jdbuchanan71 

I have single table. I need to  get the (sum of Actual hours by an Employee )/ Sum(of Actual Hours in that Project irrespective of Employee) in a DAX measure. Can you please suggest how can I do it.

Sample Output

For example if I put a slicer for employee A

ProjectID	Actual hours	ProjectHours	Measure
1	17	36	17/36
4	9	19	9/19

Sample Table DATA: 

ProjectID	Employee ID	Actual Hours
1	A	12
2	A	13
3	B	8
4	A	9
4	B	10
1	C	9
1	A	5
1	B	10

[jdbuchanan71]

Hello @Anonymous 

Give this a try.

Measure = 
VAR _Employee = SUM ( YourTable[Actual Hours] )
VAR _Project = CALCULATE ( SUM ( YourTable[Actual Hours] ), ALL ( YourTable[Employee ID] ) )
RETURN DIVIDE ( _Employee, _Project )