Forum Discussion
Resolver ICustomize dax calculation to keep the same value regardless of the filter selected for the visual?
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.
Community SupportHello 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!!
