Customize dax calculation to keep the same value regardless of the filter selected for the visual?

[Mk60]

I wonder if there is way to adjust existing dax string to keep the same values regradless of the filter selected?

I have this measure % which changes accordingly and accurately when I use "Region" filter, (have several different regions). I want to have another measure % to use as second % with each reagion selected, but need the second % to remain the same/constant for each individual filtered region? Basically to compare %s for regional line trend with cumulative % for all regions. Please advise. Thank you!

My "Region" filter is in my Query1Loan table as: "Query1Loan[Region]"

[v-ssriganesh · Accepted solution]

Hello @Mk60,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have carefully reviewed your query regarding customizing the DAX calculation to keep the percentage value constant across Region selections.

To better assist you, I have reproduced your scenario using my sample data and I am pleased to inform you that I was able to achieve the expected output as per your requirement.

I have used the below DAX measures:

• Dynamic % Measure (changes with Region filter):

DPD30% =

DIVIDE(

    SUM(Query1Loan[DPD_30]),

    SUM(Query1Loan[GL_BALANCE])

)

• Constant % Measure (ignores Region filter and stays same across Regions):

DPD30%_AllRegions =

DIVIDE(

    CALCULATE(SUM(Query1Loan[DPD_30]), ALL(Query1Loan[Region])),

    CALCULATE(SUM(Query1Loan[GL_BALANCE]), ALL(Query1Loan[Region]))

)

For the visual setup, I used a Line Chart with MonthYear placed on the X-axis. I added both measures, DPD30% and DPD30%_AllRegions, to the Y-axis Values. Additionally, I included Region as a slicer to allow dynamic filtering. As a result, the DPD30% measure dynamically changes based on the selected Region, while the DPD30%_AllRegions measure remains constant, reflecting the cumulative percentage across all Regions regardless of the slicer selection.

For your reference, I have attached the .pbix file used for testing this scenario to this post.
Please feel free to download and review it.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Mk60]

Thank you so much for taking the time to look into this. As I did not receive any other suggestion, in the meantime, I was able to resolve this by using calculated column with this dax, just in case anyone finds this helpful: 

DPD30%ALL = SUMX(FILTER('Query1Loan', 'Query1Loan'[YEAR] = EARLIER('Query1Loan'[YEAR])),'Query1Loan'[DPD_30])/

    SUMX(FILTER('Query1Loan', 'Query1Loan'[YEAR] = EARLIER('Query1Loan'[YEAR])),'Query1Loan'[GL_BALANCE])

However, I prefer your solution as calculated Mesure, which takes less time and space in the large model. Once again I trully apprecite you and your solution works perfect as well. Thanks so much!!

[Mk60]

I am not sure if I can provide any additional information on this, or perhaps there is no other possible solution out there right now? Just wanted to make sure if I was not very clear on what I wanted to do here? Please advise!

[rajendraongole1]

Hi @Mk60 -If you want the denominator to be constant across all Regions, you should remove the Region filter

can you check the below measure:

DPD30%_AllRegions =
SUM(Query1Loan[DPD_30]) /
CALCULATE(
SUM(Query1Loan[GL_BALANCE]),
ALL(Query1Loan[Region])
)

Hope this helps.

[Mk60]

I tried it and when I include it in my table with one Region selected it does not provide accurate %. The yellow highlighted cell should be 0.54%, as column2 and column3 have different number for all regions combined, and should stay the same for any region I would filter, (please see my snip below). When I use it in the table without any regiona filter, it provides accurate calculation exactly the same as my initial "DPD30%"? I have tried a few options using ALLEXCEPT, etc, but just can't get to the right. Thoughts?