Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mirae
Frequent Visitor

Percentage of row total excluding blanks?

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 -

Mirae_0-1657118788537.png

 Is there a way to calculate these proportions exluding blanks?

- Thanks all! 

1 ACCEPTED SOLUTION
rbriga
Impactful Individual
Impactful Individual

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

  

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

View solution in original post

4 REPLIES 4
rbriga
Impactful Individual
Impactful Individual

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])))
         )
)
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Mirae
Frequent Visitor

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)

Mirae_0-1657118739155.png

 

 

rbriga
Impactful Individual
Impactful Individual

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

  

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Mirae
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors