How to create this measure with a fixed denominator

[Hakuraki]

Hello community,

I am very new to DAX and I would like to create a measure that I use in one of my excel reports that I am trying to migrate to PowerBi.

This is my measure right now:

It is straight forward -- It is supposed to divide the Dynamic Measure (for example Value Sales) for selected "CustomerX". I have a slicer for Market that is filtering the Dynamic Measure. So for example the Numerator should calculate Value Sales in MarketX for CustomerX. The Denominator should calculate the Value Sales for CustomerX but in Total Market specifically.

Now comes the tricky part. The "Total Market" is part of the Market Table as are all the other markets. This is kind of unlucky, this is how we are recieving our data. We have all the markets together in the data, so the Total Market is just another market in the table. It does not necessarily mean that the Total Market is sum of other markets either.

The measure obviously does not work. Or rather works just for the Total Market which gives 100%. Is there a workaround this issue? I need to select other markets through the slicer without changing the denominator context.

Thank you for your help!

[Ashish_Mathur]

Hi,

Share some sample data to work with and show the expected result.

[v-pnaroju-msft]

Hi Hakuraki,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @FarhanJeelani for your response.

Hi Hakuraki,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @FarhanJeelani to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[FarhanJeelani]

Hi @Hakuraki ,

You need the denominator to stay fixed on “Total Market” even as the Market slicer changes the numerator. Do that by explicitly fixing the Market context for the denominator while letting the numerator react to slicers.

Keep the numerator as your current dynamic measure (Value Sales for the selected Customer in the selected Market).
Create a denominator measure that ignores the Market slicer but still respects the Customer filter.

A practical pattern (adapt to your model):

Denominator (example ideas — pick one that fits your schema):

Ignore market but keep the customer: CALCULATE( [Dynamic Measure], REMOVEFILTERS('Market') )

If you need to force “Total Market” specifically, you can add a filter: CALCULATE( [Dynamic Measure], FILTER( ALL('Market'), 'Market'[Market] = "Total Market" ) )

If you have to ensure the customer stays filtered (e.g., via slicer): CALCULATE( [Dynamic Measure], REMOVEFILTERS('Market') ) -- plus make sure the Customer filter keeps its context (usually by default)

A complete, drop-in pattern

HakurakiRatio = VAR Num = CALCULATE( [Dynamic Measure] ) -- this uses current Market + Customer VAR Den = CALCULATE( [Dynamic Measure], FILTER( ALL('Market'), 'Market'[Market] = "Total Market" ) )

RETURN IF( NOT ISBLANK(Num), DIVIDE(Num, Den) )

Please mark this post as soulution if it helps you. Appreciate Kudos.