How to apply a slicer only on one column but on on an other in a ratio formula.

[Anonymous]

Hi,

Here is an example :

numerator	denominator	type	date
1	3	a	01/01/2020
2	3	a	01/01/2020
3	3	b	01/01/2020
1	3	a	02/01/2020
2	3	a	02/01/2020
3	3	b	02/01/2020

I need a slicer to select the type : "a", "b" or both of them. But I want to keep all the denominator when i select "a" or "b"

So for the both dates, when i select "a" i sould have (1+2)/(3+3+3) and for "b" (3)/(3+3+3) but when i select a type in my slicer, i have the selection for both numerator and denominator :

formula = CALCULATE( SUM ( 'Table'[num] ))
          / CALCULATE(SUM('Table'[den]),ALL('Table'[den],'Table'[den]))

I understand that "ALL" is not the good function but maybe my whole idea is bad to do that, so I prefer to ask you... 

Thanks 🙂

[v-kelly-msft]

Hi  @Anonymous ,

Have you checked my question?Could you pls make an explanation about it?I would get to work on your issue once I get more details.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @Anonymous ,

Could you pls explain why the denominator is 3+3+3?And why numerator is 1+2?If you choose "a",it should be (1+2+1+2)/(3+3+3+3+3+3+3) if you wanna keep all the denominator.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[sevenhills]

I am not sure what you are trying from your post.

I tried as measure and measure 2 and see the ouput

Measure = Sumx('Table (2)', 'Table (2)'[numerator]) / sum('Table (2)'[denominator])

Measure 2 = Sum('Table (2)'[numerator]) / Calculate ( sum ( 'Table (2)'[denominator]), All('Table (2)')) 

Hope this is the one you need

( Measure 2 is Ratio of Total irrespective of filter, FYI)

- SV

[Anonymous]

@sevenhills did you see my answer ?

[sevenhills]

It was not clear, what output you are expecting from your post? If you give the output and sample, then I can see whether I can provide the formula... 

Thanks

[Anonymous]

Hi,

Thanks it's almost what I want. I forgot an information, I want denominator keep the selection on the date (and the numerator too of course) ! (sorry)

So I would have