Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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!
Solved! Go to 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] ) ) )
)
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])))
)
)
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)
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] ) ) )
)
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())
)
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |