Dynamic numerator and Denominator based on different selections of the same filter

[Franc_98]

Hello users!

I am struggling to find a clever way to get different variances between measure depending on the user's selection.

To be clear: I have one slicer "Period" that shows sales according to certain moments (es christmas, easter, summer).

I need to make a variance based on the user selection. For example sales from christmas / sales easter but i need it dynamic, so creating different measures for each period won't be a solution.

I want the user to select the period for the numerator and another period for the denominator. The slicer "Period" would be the same (copied two times?).

Does anyone have an idea on how to do it? 

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @Franc_98 ,

According to your description, I think it can't be done with one slicer, depite we select two values in one slicer, it can't be recognized which one is numerator and which is denominator. So here's my solution.

1.Create a new table, don't make relationship with the fact table.

Period =
VALUES ( 'Table'[Period] )

2.Create a measure.

Measure =
MAX ( 'Table'[Sales] )
    / MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Period] = SELECTEDVALUE ( Period[Period] ) ),
        'Table'[Sales]
    )

Then put the two Period columns into two seperate slicer, here in my sample, Period from the fact table is numerator and Period from the new table is denominator. Get the correct result:

I attach my sample below for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang