Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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[It...
Need Help
TeigeGao's avatar
TeigeGao
Solution Sage
7 years ago

Hi Anonymous ,

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

       

       

       

       

       

       

       

       

       

       

       

       

       

      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's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        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