Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filtering out multiple values for division calculation.

Hello,

I would like to do the caluation (Cat) / (Cat + Dog) from a column. But I have two other metrics I don't want to include in the calauation (Mouse & Rabbit), and would would like to filter out.

The [NoPet] measure is a count of all the pets. 

Here is my code but it is not working. 

Pets C/D = DIVIDE(CALCULATE([NoPet],'Animals'[Pets]IN {"Cat"}),CALCULATE([NoPet],FILTER('Animals','Animals'[Pets] <> "Mouse" || 'Animals'[Pets] <> "Rabbit")))
 
Could you please suggest workable and efficent way of doing this caluation? 
 
Thanks!
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure what visualization you want to present the result, but please try something like, 

     

    Pets C/D =
DIVIDE (
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat" } ),
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat", "Dog" } )
)

     

    Or,

     

    Pets C/D =
DIVIDE (
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat" } ),
    CALCULATE (
        [NoPet],
        FILTER ( 'Animals', 'Animals'[Pets] <> "Mouse" && 'Animals'[Pets] <> "Rabbit" )
    )
)

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure what visualization you want to present the result, but please try something like, 

     

    Pets C/D =
DIVIDE (
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat" } ),
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat", "Dog" } )
)

     

    Or,

     

    Pets C/D =
DIVIDE (
    CALCULATE ( [NoPet], 'Animals'[Pets] IN { "Cat" } ),
    CALCULATE (
        [NoPet],
        FILTER ( 'Animals', 'Animals'[Pets] <> "Mouse" && 'Animals'[Pets] <> "Rabbit" )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you! The top one works perfectly.  I'm using it in a line chart to compare the percentage of cats to dogs over time. 

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    Hi there!

    Can you provice de DAX code of [NoPet].

    In the meantime you can try:

    Pets C/D =
DIVIDE(
    CALCULATE( [NoPet], 'Animals'[Pets] = "Cat" ),
    CALCULATE( [NoPet], 'Animals'[Pets] IN { "Cat", "Dog" } )
)

    Let me know if that helps!