How to keep filters on table visualization when adding a new measure

[dh76_PBI]

Hi all, 

I have a problem where when I add a measure to a table visualization, the slicers affecting the visualization seem to stop working.  

I have my data in a snowflake schema. I am illustrating using example data. In reality, I have several more dimension tables. In this case, however, let's pretend we have one fact table, a Location table, and a lookup table that connects to the Location table. An example of this schema is seen below. 

Here is the example Fact Table: 

I have several groups in several locations competing in challenges. There is a Challenge Start Date and an optional Challenge End Date. If the Challenge End is blank, then the challenge is ongoing and the Status within the Fact Table is "Closed". Some groups have received a "Penalty" (1= penalty applied, 0=no penalty).

Here is the Location table:

And the Lookup Table: 

When I insert a table visual containing Region from the Lookup table and Division from the Location table, it looks like this:

The above visualization can be filtered by District as shown below:

I have created two measures: one to count the Open challenges, and one to sum the number of Open challenges receiving penalties. These measures are written as follows:

Count Open = CALCULATE(COUNT('Fact Table'[ID]),'Fact Table'[Status] = "Open")

Sum Penalty Open = CALCULATE(SUM('Fact Table'[Penalty]),'Fact Table'[Status] = "Open")​

The numbers make sense in my visual, and the table can still be filtered by district (in the below example, Division B is missing because its challenge was Closed, and this table is looking only at Open challenges).

However, when I create a measure that outputs a ratio using the measures I created above and add it to the table visualization, the District filter no longer applies. 

The KPI is written as follows:

KPI Penalty Open = 1 - ([Sum Penalty Open]/[Count Open])

This is the resulting Table visualization:

As you can see, it is showing a result for all Regions. Only Divisions A, B, and C are in District 1, corresponding with Regions AB and AC. Howver, despite the District filter being applied, all Regions are shown in the table visual. How do I prevent this from happening?

I believe this may have something to do with how zeros are calculated, but I am at a loss.

Here and here are two forum answers which seemed close, but I didn't 100% understand how this applies to my case. 

Thank you in advance. 

[IIPowerBlog]

Hi @dh76_PBI  while I fully agree with @OwenAuger  and the solution, maybe this is simpler than expected:

Try the simple following steps: 

First:

1a. Your Lookup Table should include the ID column of the Location table 

OR

1b. Your Location Table should include the Region ID

Have you related these two tables correctly? The ID column of the Location table has to be your KEY 

Then (optinal if the first doesnt work out!) 

1. Instead of the COUNT measures, use COUNT directly on the fields 'Open' and 'Penalty Open' on the Visualization of your table. 

2. For your KPI calculation create a calculated field.

3. Remove the measures from your table and add the Calculated Field from step 2. 

Does this work?

[OwenAuger · Accepted solution]

Hi @dh76_PBI 

Yes, this is indeed to do with zeros, specifically that BLANK is equal to zero, so that 1 - BLANK evaluates to 1 or 100%.

You could rewrite your measure a few ways to avoid returning a result when the ratio of the two measures is blank (I would probably prefer the first):

KPI Penalty Open =
VAR PenaltyOpen =
    [Sum Penalty Open]
VAR CountOpen =
    [Count Open]
RETURN
    DIVIDE ( CountOpen - PenaltyOpen, CountOpen )

or

KPI Penalty Open =
IF (
    NOT ISEMPTY ( 'Fact Table' ),
    1 - DIVIDE ( [Sum Penalty Open], [Count Open] )
)

or

KPI Penalty Open =
VAR PenaltyOpen =
    [Sum Penalty Open]
VAR CountOpen =
    [Count Open]
VAR Ratio = DIVIDE ( PenaltyOpen, CountOpen )
RETURN
    IF (
        NOT ISBLANK ( Ratio ),
        1 - Ratio
    )

Regards,

Owen

[dh76_PBI]

Thank you! This worked. 

I also used the solution from here for some of my other measures, in case this helps others.