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
zipke
Helper I
Helper I

Percentage of COUNT without null() values

Hi all,

 

I am trying to create a DIVIDE measure to calculate the percentage of a count over two specific groups (group1 and group2).

 

Code for the count:

Count = CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1],'Classes'[Group2]))

 

So far so good as you can see in the image below. I used the following DAX for the %:

Count % per Group1 = DIVIDE(
    [Count],
    CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1]),ALLEXCEPT(Classified,Classified[Group1])
    )
    ,0
    )

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The issue now are the group2 with null() values (e.g. the first line in the table in the screenshot). There are 93 counts for this group1 with an empty group2.

 

When I filter in the visuals on "is not blank" power BI still remembers there is still a count of 93 for a null() value in group2. What I want now is to get the total count for group1 to 48 (141-93) and then calculate the percentages based on this 48 as a total. Thus family C22J0000 will have a percentage of 35% (17/48) instead of 12%.

 

I am trying to do this by changing COUNTA to COUNTAX in both my nominator and denominator, but nothing works...

 

Thanks in advance for your help!

12 REPLIES 12
zipke
Helper I
Helper I

Hi all,

 

I am trying to create a DIVIDE measure to calculate the percentage of a count over two specific groups (group1 and group2).

Code for the count:

Count = CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1],'Classes'[Group2]))

So far so good as you can see in the image below.

 

I used the following DAX for the %:

Count % per Group1 = DIVIDE(
    [Count],
    CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1]),ALLEXCEPT(Classified,Classified[Group1])
    )
    ,0
    )

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The issue now are the group2 with null() values (e.g. the first line in the table in the screenshot). There are 93 counts for this group1 with an empty group2.

 

When I filter in the visuals on "is not blank" power BI still remembers there is still a count of 93 for a null() value in group2. What I want now is to get the total count for group1 to 48 (141-93) and then calculate the percentages based on this 48 as a total. Thus family C22J0000 will have a percentage of 35% (17/48) instead of 12%.

 

I am trying to do this by changing COUNTA to COUNTAX in both my nominator and denominator, but nothing works...

 

Thanks in advance for your help!

 

 

TeigeGao
Solution Sage
Solution Sage

Hi @zipke ,

In your scenario, we can use the calculate() with Filter('Classes'[Group2] <> blank()) function, besides, could you please share your pbix file?

Best Regards,

Teige

Hi @TeigeGao,

 

It is difficult to share my pbix as it has confidential information.

Can you help me with the setup of the DAX? Where exactly should I add this FILTER <> BLANK() to my code?

 

Thanks!

Cmcmahan
Resident Rockstar
Resident Rockstar

Adding it to your list of filters in your count should work:

Count = CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1],'Classes'[Group2]),FILTER(Classes[Group2]<>BLANK()))

Also, if you're having problem with this, you can try using COUNTBLANK() to get a count of the blanks in your data, and subtract that from the amount and totals. 

@Cmcmahan  Thank you, this worked for the count!

 

Unfortunately my percentage calculation is still of now. It still calculates everything to the old total count of 141.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

The code for my percentage count is:

Count % per Group 1 = DIVIDE(
    [Count],
    CALCULATE(COUNTA(Classified[Item number]),ALLEXCEPT(Classified,Classified[Group1]),ALLEXCEPT(Classified,Classified[Group1])
    )
    ,0
    )

Thanks in advance!

Cmcmahan
Resident Rockstar
Resident Rockstar

You should be able to fix it in the same way. Right now you're using all filters that are already applied to Classified[Group1].  Just add another filter that gets rid of null values from your total like above.  If you're still having trouble, let me know and I can figure out the exact syntax for you

The way I wrote my code now, it doesn't seem to work.

My code for Count = 

 
Count = CALCULATE(
    COUNTA(Classified[Item number]),
    ALLEXCEPT(Classified,Classified[Group1], 'Classes'[Group2]),
    FILTER('Classes','Classes'[Group2]<>BLANK())
)

 

And my code for the % calculation =

Count % per eClass = DIVIDE(
    [Count],
    CALCULATE(
        COUNTA(Classified[Item number]),
        ALLEXCEPT(Classified,Classified[Group1]),
        FILTER('Classes', 'Classes'[Group2]<> BLANK())
    )
    ,0
    )

I get 100% on every line now..

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

What did I do wrong? Thanks for your help @Cmcmahan !

 
Cmcmahan
Resident Rockstar
Resident Rockstar

Interesting.  So you're getting a value, but it's always equal to [Count], so you get 100% every time.  At first glance (and for many glances after that) this seems like exactly what I would do, and should work.

 

I'm curious, can you share how Classified and Classes are related?  It seems that the measure is not clearing filters correctly, or clearing the wrong filters.  Before you try the expression below, can you try replacing the  FILTER('Classes', 'Classes'[Group2]<> BLANK()) portion from your % calculation with FILTER(ALL('Classes'), 'Classes'[Group2] <> BLANK())

 

I'm looking at the documentation for ALLEXCEPT, and it seems to only clear filters from the named table. I think you're clearing filters from Group1 with the ALLEXCEPT, but because the Group2 data is in a different table, it's keeping those filters active.  

 

You should also be able work around this issue by using this expression, but it's more of a workaround than it needs to be.

 

Count % per eClass = DIVIDE(
    [Count],
    CALCULATE(
        COUNTA(Classified[Item number]),
       FILTER(ALL(Classified),Classified[Group1]=SELECTEDVALUE(Classified[Group1]))
    )
    ,0
    )

This should count Item Numbers where Group1 is the same as the Group1 in the current context.

Hi @Cmcmahan 

 

I tried your first option: this gives me very low percentages. I guess it calculates the percentage over ALL of the Group 2's now, and not just the Group 2 as part of the Group 1 (column 1)

 

The workaround option gives me the same result as my old code: everything is still calculated to the old total of 141 (column 2).

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The relationship between Classes and Classified is a 1:1 based on Item numbers. 1 item number has only 1one Group 1 and only one Group 2.

Cmcmahan
Resident Rockstar
Resident Rockstar

So this time I actually created a sample set of data that replicates your format to play with in an actual test.  Here's what I found works.  The only difference is that I have Group1, Item Number, and Group2 all in the same table. If you run into issues because of this, let me know, and I can complicate the test data.

 

Count % per eClass = DIVIDE(
    [Count],
    CALCULATE(
        COUNTA(Classified[Item number]),
        FILTER(ALLEXCEPT(Classified,Classified[Group1]), 'Classes'[Group2]<> BLANK())
    )
    ,0
    )

So this seems to have been a problem with chaining filters.  I'm still learning DAX myself, and this has been very interesting issue.  

Thanks for putting so much of your time into this @Cmcmahan ! It is a bit annoying I will have to but all fields into one table though 😞 It's strange how difficult the question turns out to be.. It "looks" so simple, but clearly isn't..

 

If anyone knows how I can solve my issue, without me having to change my tables and just tweak the code a bit, please do so! It feels like I will need this many more times in the future thus the cleaner and easier the solution, the better!

 

Thanks in advance!

Cmcmahan
Resident Rockstar
Resident Rockstar

What happens when you try the above code snippet without combining tables?

 

EDIT: ahh, I see. you can't refer to the anything from Classes in the FILTER.  You may be able to use RELATED(Classes[Group2]) to refer to those fields.  Or maybe COUNTA(Classes[Group2]...) instead of COUNTA(Classified[Item number]...), since it will automatically not count null values.

 

Could you share a redacted set of data with just the necessary fields and edge cases that I can mess around with?  I feel like we're super close, but I can't figure out a good way to replicate your structure 

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.