Percentage of row total excluding blanks?

[Anonymous]

Hey all,

Trying to calculate percentage of row total exluding blanks.

In this example, it's the proportions of individuals that fall into certain BMI categories for each year. 

To do this, I'm using the DAX:

Proportions = DIVIDE(COUNT('Fact Table'[PersonKey]),CALCULATE(COUNT('Fact Table'[PersonKey]),ALL('Measurement Details'[BMI Category])))

Issue is, some of the individuals don't have a BMI category stated, so there are blanks like so -

 Is there a way to calculate these proportions exluding blanks?

- Thanks all! 

[rbriga]

You may add to the CALCULATE:

Proportions = 
DIVIDE(
 COUNT('Fact Table'[PersonKey]),
 CALCULATE(
    COUNT('Fact Table'[PersonKey]),
    REMOVEFILTERS('Measurement Details'[BMI Category]),
    KEEPFILTERS(NOT ( ISBLANK('Measurement Details'[BMI Category])))
         )
)

[Anonymous]

Thank you for such a prompt suggestion! Gave it a try but it's still considering those blanks (hid them in the filters in this example)

[rbriga · Accepted solution]

Two approaches can solve this:

1. Assuming you filter out \ slice out the blank BMI

% In Population = 

    DIVIDE (
        COUNT ( 'Fact Table'[PersonKey] ),
        CALCULATE (
            COUNT ( 'Fact Table'[PersonKey] ),
            ALLSELECTED('Measurement Details'[BMI Category])
        )
    )

2. Assuming you don't manually filter out anything:

CALCULATE (
    DIVIDE (
        COUNT ( 'Fact Table'[PersonKey] ),
        CALCULATE (
            COUNT ( 'Fact Table'[PersonKey] ),
            REMOVEFILTERS ('Measurement Details'[BMI Category]  ),
            KEEPFILTERS ( NOT ( ISBLANK ( 'Measurement Details'[BMI Category] ) ) )
        )
    ),
    KEEPFILTERS ( NOT ( ISBLANK ( 'Measurement Details'[BMI Category] ) ) )
)

[Anonymous]

ALLSELECTED worked an absolute treat. Thank you so much! 😁

The section option didn't want to work for me, but I found tweaking it to the following did - sharing in case this helps anymore else

Pupil % = CALCULATE (
    DIVIDE (
        COUNT ( 'Fact Table'[PersonKey] ),
        CALCULATE (
            COUNT ( 'Fact Table'[PersonKey] ),
            REMOVEFILTERS ('Measurement Details'[BMI Category]  ),
            KEEPFILTERS ('Measurement Details'[BMI Category] <>BLANK()) 
        )
    ),
    KEEPFILTERS ('Measurement Details'[BMI Category] <>BLANK())
)