topic Re: Percentage of row total excluding blanks? in DAX Commands and Tips

Percentage of row total excluding blanks?

Anonymous — Wed, 06 Jul 2022 14:46:42 GMT

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!

Re: Percentage of row total excluding blanks?

rbriga — Wed, 06 Jul 2022 10:49:34 GMT

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]))) ) )

Re: Percentage of row total excluding blanks?

Anonymous — Wed, 06 Jul 2022 14:45:55 GMT

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)

Re: Percentage of row total excluding blanks?

rbriga — Wed, 06 Jul 2022 11:19:59 GMT

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] ) ) ) )

Re: Percentage of row total excluding blanks?

Anonymous — Wed, 06 Jul 2022 14:39:26 GMT

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()) )