cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Unable to Get Correct Percentage when Filter or Slicer Used

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.

 

Percentage Stmt without Filter.PNGPercentage Stmt with Revenue Filter.PNG

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?  

1 ACCEPTED SOLUTION

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 )

View solution in original post

2 REPLIES 2
Community Support
Community Support

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

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 )

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors