## Desktop

Highlighted
Regular Visitor
Posts: 15
Registered: ‎01-20-2018

# Unable to Get Correct Percentage when Filter or Slicer Used

[ Edited ]

I have a table with percentage of revenue. It works so long as I don't filter the accounts with a slicer or visual filter. When I use a slicer or visual filter, revenue goes to zero and I don't get the percent of revenue.

I have tried a number of formulas to try to overcome the slicer selection, but nothing has worked. Is there a way to get the value for revenue despite filtering it out with a slicer or visual filter?

The formula for % Stmt looks like this (ALLSELECTED because I was using year selections):

```% Stmt =
VAR AnnualRevenue = CALCULATE( [Total Revenue], Dates, ALLSELECTED( ))

RETURN
DIVIDE( SUM( MyTable[Value] ), AnnualRevenue, 0 )```

This one does return a percentage, but it makes no sense:

```Another % Revenue Formula = VAR AnnualRevenue =
CALCULATE(
SUM( MyTable[Value] ),
FILTER( ALL( MyTable ), MyTable[Account] = "Revenue" )
)
RETURN
DIVIDE( SUM( MyTable[Value] ), AnnualRevenue, 0 )```

I have tried using CALCULATETABLE in place of FILTER and I still don't get the correct revenue amount. Likewise, ALL does not help.

How can I get the correct revenue amount regardless of the selection in a visual filter or slicer?

Accepted Solutions
Regular Visitor
Posts: 15
Registered: ‎01-20-2018

## Re: Unable to Get Correct Percentage when Filter or Slicer Used

Thank you, @v-yuta-msft, you put me on the right track. Here is what worked:

(1) As background, the formula for the [Total Revenue] measure:

```Total Revenue =
SUMX(
FILTER( MyTable, MyTable[Account] = "Revenue" ),
MyTable[Value]
)```

(2) The % Stmt measure. ALLEXCEPT is the key.

```% Stmt =
//Putting Dates in ALLEXCEPT is what worked.
VAR AnnualRevenue =
CALCULATE(
[Total Revenue],
ALLEXCEPT( MyTable, Dates ), MyTable[Account] = "Revenue"
)

RETURN
DIVIDE( SUM( MyTable[Value] ), AnnualRevenue, 0 )```

All Replies
Community Support Team
Posts: 701
Registered: ‎02-06-2018

## Re: Unable to Get Correct Percentage when Filter or Slicer Used

Hi CS,

Is [Tota Revenue] a measure based on table Dates? If it is, modify your DAX formula like this and try again.

```% Stmt =
VAR AnnualRevenue = CALCULATE( [Total Revenue], ALLSELECTED(Date[Year Account]))
RETURN
DIVIDE( SUM( MyTable[Value] ), AnnualRevenue, 0 )```

Regards,

Jimmy Tao

Regular Visitor
Posts: 15
Registered: ‎01-20-2018

## Re: Unable to Get Correct Percentage when Filter or Slicer Used

Thank you, @v-yuta-msft, you put me on the right track. Here is what worked:

(1) As background, the formula for the [Total Revenue] measure:

```Total Revenue =
SUMX(
FILTER( MyTable, MyTable[Account] = "Revenue" ),
MyTable[Value]
)```

(2) The % Stmt measure. ALLEXCEPT is the key.

```% Stmt =
//Putting Dates in ALLEXCEPT is what worked.
VAR AnnualRevenue =
CALCULATE(
[Total Revenue],
ALLEXCEPT( MyTable, Dates ), MyTable[Account] = "Revenue"
)

RETURN
DIVIDE( SUM( MyTable[Value] ), AnnualRevenue, 0 )```