Forum Discussion
Percentage changes when dashboard is published
Hello! I have a dashboard that I created a measure to calculate the percentage of on-time deliveries vs total deliveries.
Calculated field that is a text field (it doesn't allow me to change this to a whole number)
Any ideas why this is happening and how to fix the issue?
Hi SteffanieJ
What jumps out first is that the numerator and denominator are not based on the same level of detail, and that is probably the main reason the percentage becomes unstable.
In your on-time measure, you are doing a DISTINCTCOUNT on a calculated text field built from Sales Document and the flag, while in the denominator you are counting distinct Delivery values. If one sales document can be tied to multiple deliveries, then the ratio is not really comparing like with like, so it can behave differently depending on filter context, visual interactions, or how the Service re-evaluates the model after publishing.
I would also be a little careful with the calculated text column approach here. It may work in some cases, but it is a fairly fragile way to define the numerator. In general, for a percentage like this, both sides of the calculation should be counting the same business entity. So if the KPI is supposed to be on-time deliveries out of total deliveries, then both measures should be based on Delivery, not Sales Document on one side and Delivery on the other.
I would try rewriting it like this:
Total Delivery OnTime =
CALCULATE(
DISTINCTCOUNT('Ord to Del'[Delivery]),
'Ord to Del'[RDD Created On Time Flag] = "1",
'Ord to Del'[Route Type] <> "CPU"
)Total Distinct Delivery Count =
CALCULATE(
DISTINCTCOUNT('Ord to Del'[Delivery]),
'Ord to Del'[Route Type] <> "CPU"
)RDD OnTime =
DIVIDE([Total Delivery OnTime], [Total Distinct Delivery Count], 0)If the actual business logic is meant to be based on sales documents rather than deliveries, then the denominator should also use Sales Document so the comparison stays consistent.
The fact that the value looks correct at first and then flips a couple of minutes later in the Service makes me think this is more likely a model/query behavior issue than a formatting issue. I would check whether the report is using DirectQuery, a composite model, auto page refresh, RLS, or even visual interactions that might be changing the filter context after the page finishes loading.
I would also verify whether RDD Created On Time Flag is truly stored as text "1" everywhere, and not numeric 1 in some parts of the model, because inconsistent data types can sometimes create confusing behavior between Desktop and Service.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Hi SteffanieJ
What jumps out first is that the numerator and denominator are not based on the same level of detail, and that is probably the main reason the percentage becomes unstable.
In your on-time measure, you are doing a DISTINCTCOUNT on a calculated text field built from Sales Document and the flag, while in the denominator you are counting distinct Delivery values. If one sales document can be tied to multiple deliveries, then the ratio is not really comparing like with like, so it can behave differently depending on filter context, visual interactions, or how the Service re-evaluates the model after publishing.
I would also be a little careful with the calculated text column approach here. It may work in some cases, but it is a fairly fragile way to define the numerator. In general, for a percentage like this, both sides of the calculation should be counting the same business entity. So if the KPI is supposed to be on-time deliveries out of total deliveries, then both measures should be based on Delivery, not Sales Document on one side and Delivery on the other.
I would try rewriting it like this:
Total Delivery OnTime =
CALCULATE(
DISTINCTCOUNT('Ord to Del'[Delivery]),
'Ord to Del'[RDD Created On Time Flag] = "1",
'Ord to Del'[Route Type] <> "CPU"
)Total Distinct Delivery Count =
CALCULATE(
DISTINCTCOUNT('Ord to Del'[Delivery]),
'Ord to Del'[Route Type] <> "CPU"
)RDD OnTime =
DIVIDE([Total Delivery OnTime], [Total Distinct Delivery Count], 0)If the actual business logic is meant to be based on sales documents rather than deliveries, then the denominator should also use Sales Document so the comparison stays consistent.
The fact that the value looks correct at first and then flips a couple of minutes later in the Service makes me think this is more likely a model/query behavior issue than a formatting issue. I would check whether the report is using DirectQuery, a composite model, auto page refresh, RLS, or even visual interactions that might be changing the filter context after the page finishes loading.
I would also verify whether RDD Created On Time Flag is truly stored as text "1" everywhere, and not numeric 1 in some parts of the model, because inconsistent data types can sometimes create confusing behavior between Desktop and Service.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hello,
I’m not completely sure, but when a number changes a few minutes after publish it’s often not the DAX itself, more like the Service recalculating things differently, usually due to relationships, filters, or even data type quirks
your first calc being text-based is a bit suspicious, DISTINCTCOUNT over a concatenated text field can behave inconsistently between Desktop and Service, especially with blanks or duplicates, I’d try rebuilding that logic using a proper numeric flag (like 0/1) instead of text and then count rows or use DISTINCTCOUNT on a stable ID
also worth checking if there’s any difference in the dataset after publish, like incremental refresh, RLS, or auto date/time messing with filters
feels like one of those cases where the model is just slightly fragile and the Service exposes it
Best regards,
DanieleHi SteffanieJ,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Ritaf1983 and DanieleUgoCopp for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi SteffanieJ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.
