Forum Discussion
Locking Values on a denominator DIVIDE formula
Hello,
Power BI Amateur here.
I´m currently working with data from a system that tracks Processes in the organization (stored in a table called Results).
As i´m currently working on closing the solution i got myself into some issues that need solving.
1. My idea was to create a formula that allows me to determine the Percentage of Selected Cases, meaning this measure would track the amount of cases that are selected via Slicers or directly in the graphics i have and DIVIDE it by the Total of Cases, therefore the need to make the denominator static and completely locked.
2. As things are right now the Measures i have to calculate the amount of cases are:
COALESCE(DISTINCTCOUNT(Results[NUP]),0)
//For the output pretended DISTINCTCOUNT is necessary, as redundant Processes need to be in the system for pontential future tweaks.
-% Processes =
Output Pretended: I want to find a way to LOCK THE DENOMINATOR to return me that 242 Total of Processes, but to make it
scalable enough for possible new Processes added in the System. With that i can calculate the % of Selected Processes, as long as [Registered Processes] keeps on changing when a Slicer or a iteraction with the Visualizations is applied.
Attention:i I´ve tried the ALL keyword but it bypasses every Page Filter i have, for example if the Process is Planned or not, in which is not pretended (for the record, it returned over 2100 lines of Processes in the the System, which does not correspond with the output needed for the PowerBI solution i´m creating, as i´m only working with Planned Processes in this Dashboard)
Thank you so much for the attention,
DVD_RDRGES
Hi GeraldGEmerick.
Happy for your reply.
In fact after searching a little bit further i found a similiar solution that goes by this:Total Registered Processes =CALCULATE(DISTINCTCOUNT(Results[NUP]),ALL(Results),VALUES(Resultados[Planned]))
You can also found a similiar situation via this link:
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/with-static-denominator/m-p/4675151
Thank you for your response,
Best RegardsHi DVD_RDRGES,
Thank you for your follow-up and for outlining the solution you implemented.
Your approach using ALL(Results) with VALUES(Results[Planned]) is correct, as it ensures the denominator remains unaffected by visual interactions while honoring the Planned filter context.
This keeps your measure tied to the total planned processes and allows for scalability as new records are added.
To clarify:
Numerator ([Registered Processes]) adapts to slicers and visual interactions.
Denominator ([Total Registered Processes]) stays fixed to the full set of planned processes, ensuring a consistent percentage calculation.
This setup matches the scenario you described, and your measure now accurately supports your percentage logic requirements.
Thank you.
5 Replies
- GeraldGEmerick
Memorable Member
DVD_RDRGES You can use COUNTROWS and ALL. ALL removes all context. So something like COUNTROWS( ALL( 'Table' ) ) or something similar should work.
Hi GeraldGEmerick.
Happy for your reply.
In fact after searching a little bit further i found a similiar solution that goes by this:Total Registered Processes =CALCULATE(DISTINCTCOUNT(Results[NUP]),ALL(Results),VALUES(Resultados[Planned]))
You can also found a similiar situation via this link:
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/with-static-denominator/m-p/4675151
Thank you for your response,
Best Regards
v-sgandrathiCommunity Support
Hi DVD_RDRGES,
Thank you for your follow-up and for outlining the solution you implemented.
Your approach using ALL(Results) with VALUES(Results[Planned]) is correct, as it ensures the denominator remains unaffected by visual interactions while honoring the Planned filter context.
This keeps your measure tied to the total planned processes and allows for scalability as new records are added.
To clarify:
Numerator ([Registered Processes]) adapts to slicers and visual interactions.
Denominator ([Total Registered Processes]) stays fixed to the full set of planned processes, ensuring a consistent percentage calculation.
This setup matches the scenario you described, and your measure now accurately supports your percentage logic requirements.
Thank you.- v-sgandrathi
Hi DVD_RDRGES,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved. Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum.
- Poojara_D12
Super User
Hi DVD_RDRGES
What you are essentially trying to achieve is a percentage measure where the numerator changes dynamically with slicers and selections, but the denominator remains locked to the total number of “planned” processes in scope (242 at the moment, but scalable as more are added). Your current approach hardcodes the denominator, which works temporarily but will not adapt to future data growth, and using ALL resets all filters, which explains why you ended up with over 2,100 processes instead of just the planned ones. A better approach is to define a separate measure for the total planned processes, using CALCULATE with a filter that only enforces the “planned” condition while ignoring the interactive slicers applied to visuals. This way, [Registered Processes] will continue to update based on user selections, while [Total Planned Processes] stays fixed to the overall scope of planned items. Then, your percentage measure becomes DIVIDE([Registered Processes], [Total Planned Processes], 0). This ensures the denominator always reflects the full planned population, scalable as more cases are added, while the numerator dynamically follows slicers and interactions, giving you the percentage of selected cases you want.
