Filtering out multiple values for division calculation.

[George_H]

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!

[PabloDeheza]

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!

[Jihwan_Kim · Accepted solution]

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

[George_H]

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.