如何用DAX计算达成80%关闭率所需的新增Closed Tickets数量？

问题背景 #

从工单系统获取的数据：

Closed Tickets: 10
Transferred Tickets: 10
Total Tickets: 20
当前关闭率: Closed tickets/Total tickets = 10/20 = 0.5 = 50%

需要计算需新增多少个Closed tickets，才能使关闭率达到80%或更高，对应方程：

(Closed Tickets + x) / (Total tickets + x) = 0.8
(10 + x) / (20 + x) = 0.8

代数推导X的解 #

先对方程进行变形求解：

1. 交叉相乘消去分母：Closed Tickets + x = 0.8 * (Total Tickets + x)
2. 展开右侧项：Closed Tickets + x = 0.8*Total Tickets + 0.8x
3. 移项将含x的项移到左侧，常数项移到右侧：x - 0.8x = 0.8*Total Tickets - Closed Tickets
4. 合并同类项化简：0.2x = 0.8*Total Tickets - Closed Tickets
5. 求解x：x = (0.8*Total Tickets - Closed Tickets) / 0.2

代入示例数值计算：x = (0.8*20 -10)/0.2 = (16-10)/0.2 = 6/0.2 =30，验证：(10+30)/(20+30)=40/50=80%，符合要求。

DAX实现方案 #

DAX没有专用函数直接求解此类方程，需将推导后的公式转化为DAX表达式。假设模型中已有Closed Tickets和Total Tickets的度量值或列，可创建如下度量值：

Required Closed Tickets to Reach 80% = 
VAR CurrentClosed = [Closed Tickets]
VAR CurrentTotal = [Total Tickets]
VAR TargetRate = 0.8
VAR CalculatedX = DIVIDE( TargetRate * CurrentTotal - CurrentClosed, 1 - TargetRate )
-- 向上取整确保满足≥80%的要求，同时避免负数结果
RETURN MAX( ROUNDUP( CalculatedX, 0 ), 0 )

代码说明 #

• DIVIDE函数用于安全除法，避免分母为0的错误
• ROUNDUP处理小数结果：若计算出X为非整数，向上取整才能保证关闭率达标
• MAX(..., 0)确保当当前关闭率已超过80%时，返回0（无需新增关闭工单）

内容的提问来源于stack exchange，提问作者Serge Inácio