Retain original percentage on filtering

[Scubadiver007]

Hi all,

If I filter this table for "yes", the percentages revert back to 100%. What would I need to do keep the original percentage.

Following on from this, how could I compare boys and girls (or other groups) but in the same table. Below, the first table is for boys and the second is for girls.

I would like to put this information into a clustered bar chart or line chart.

I am a total newbie at this!

David

[Scubadiver007]

Hi, thanks for the replies but i've been having a rethink now I have learnt a bit more about how it works.😊

I have three measures

Total_Pupils = count(EYFS[DfE])

GLD = countrows(filter(EYFS,EYFS[GLD]="Yes"))

GLD_Perc = divide([GLD]/[Total_Pupils],0)

I have these measures in a table but the percentage isn't showing. Any idea why?

[v-nuoc-msft · Accepted solution]

Hi @Scubadiver007 

Try changing your code to this:

GLD_Perc = DIVIDE([GLD], [Total_Pupils], 0)

DIVIDE function (DAX) - DAX | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Scubadiver007]

Hi,

Apologies, I wasn't clear enough. I should have said it is pupil level data so either has a "yes" or "no" recorded. The 'sex' comes from the census table and the GLD comes from the EYFS table (1-to-1 relationship). The census year can come from either table.

So can you go back one step and explain how you would create your dummy table?

Thanks

[v-nuoc-msft]

Hi @Scubadiver007 

Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.

Regards,

Nono Chen

[v-nuoc-msft]

Hi @Scubadiver007 

Thank you very much parry2k for your prompt reply.

Is your problem solved? If not, here are some suggestions:

Here's some dummy data

“Table”

The first problem: creating a measure.

Total % = 
VAR totalValues = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = MAX('Table'[Year])
    )
)
VAR totalGLD = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        'Table',
        'Table'[Year] = MAX('Table'[Year]) 
        &&
       'Table'[GLD] = MAX('Table'[GLD])
    )
)
RETURN
DIVIDE(totalGLD, totalValues)

Create a matrix.

The second question: Create a Measure.

Total SEX % = 
VAR totalValues = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = MAX('Table'[Year])
        &&
       'Table'[SEX] = MAX('Table'[SEX])
    )
)
VAR totalGLD = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        'Table',
        'Table'[Year] = MAX('Table'[Year]) 
        &&
       'Table'[GLD] = MAX('Table'[GLD])
       
    )
)
RETURN
DIVIDE(totalGLD, totalValues)

Create a Line chart.

You can get:

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Scubadiver007 np, share the calculation you are using on the visual that is showing %

[Scubadiver007]

As I said, I am a total newbie. All I've done is create a matrix and I don't know what to do next.

[parry2k]

@Scubadiver007 you need to change your measure  to either add removefilters or ALL dax function. Share your measure expression for solution.