topic Re: Filtering out multiple values for division calculation. in DAX Commands and Tips

Filtering out multiple values for division calculation.

Anonymous — Fri, 16 Sep 2022 13:02:44 GMT

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!

Re: Filtering out multiple values for division calculation.

Jihwan_Kim — Fri, 16 Sep 2022 13:19:03 GMT

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

Re: Filtering out multiple values for division calculation.

PabloDeheza — Fri, 16 Sep 2022 13:21:21 GMT

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!

Re: Filtering out multiple values for division calculation.

Anonymous — Fri, 16 Sep 2022 13:33:50 GMT

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.