How can I avoid denominator should not get filtered while calculating the percentage in beast mode

SwapnaE
SwapnaE Member
in Magic ETL

Hi,

I have a column name 'Order' with 1 to 6 values in it. I need to calculate the percentage of order 1 and show this percentage on a card in the dashboard. This percentage should change dynamically when the data gets refreshed.

I am taking unique count of orders having 1 divide by total count of orders. when i am filtering in analyzer with order 1, it gets filtering both numerator and denominator counts and i am getting 100% rather than actual percentage.

How this issue can be resolved, could you please suggest.

Thank you.

Tagged:

Best Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @SwapnaE you can use FIXED functions to achieve this in your denominator.

    Say your filter column is called ColumnB, and you want to allow a date column (DATE) to filter

    COUNT(ColumnB) FIXED (FILTER ALLOW DATE))

    You can set which columns are allowed to impact the denominator.

    This will prevent all columns except for what you allow from impacting your denominator.

    Here is a good reference article you can use to dial in the exact set up you need for your particular requirements.

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @SwapnaE you can use COUNT DISTINCT with a fixed function.

    To not allow any filters, you would do something like this.

    COUNT(DISTINCT `id`) FIXED (FILTER NONE)

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @SwapnaE you can use FIXED functions to achieve this in your denominator.

    Say your filter column is called ColumnB, and you want to allow a date column (DATE) to filter

    COUNT(ColumnB) FIXED (FILTER ALLOW DATE))

    You can set which columns are allowed to impact the denominator.

    This will prevent all columns except for what you allow from impacting your denominator.

    Here is a good reference article you can use to dial in the exact set up you need for your particular requirements.

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • DataMaven
    DataMaven Coach

    I'd like to staple this answer to my brain!

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • SwapnaE
    SwapnaE Member

    Thank you David.

    Can I use COUNT(DISTINCT ORDERID) with FIXED function and I do not want to allow any filter for the denominator.

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @SwapnaE you can use COUNT DISTINCT with a fixed function.

    To not allow any filters, you would do something like this.

    COUNT(DISTINCT `id`) FIXED (FILTER NONE)

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • SwapnaE
    SwapnaE Member

    @david_cunningham, Thank you so much. My issue got resolved with your suggestion.

  • Jbrorby
    Jbrorby Contributor

    @david_cunningham I am struggling a bit trying to do a "filter none."

    We have blood donor registrations, and some of these registrations are deferred and some are not. So the first part of the beastmode is just (count of deferred registrations)/(count of all registrations) for the deferral rate %.

    However, when registrations are deferred, they can fall into many different deferral categories. So we're wanting to be able to filter to certain deferral categories without losing any of the denominator (total registrations).

    I have tested the beastmode before adding the FIXED FILTER NONE, and so I know that everything in the beastmode is working correctly and that it is in fact adding the FIXED part that is giving me troubles, so I think I'm just not doing it correctly. The numbers no longer all go to 100% when selecting a deferral category, so that is good, but the %'s are not correct. I looked at 2 weeks where I should be getting .0717 and .089 but I'm getting .00033, so whatever I'm doing, it is way off.

    image.png
    beastm syntax.txt
    beastmode picture.PNG



Categories