我有以下格式的数据:
类别1 | 类别2 | 类别3 | 金额 | |
---|---|---|---|---|
A | D | X | 2 | |
A | E | X | 5 | |
A | E | Y | 1 | |
B | D | Z | 10 | |
B | F | X | 2 |
我创建了2个dax:第一个是分子(用于根据所有3类的概率计算堆叠量(请参考SS附带的
numerator =
var cat3Selected = SELECTEDVALUE(Stack[Category3])
var cat2Selected = SELECTEDVALUE(Stack[Category2])
var cat1Total =
CALCULATE(
IF(and(ISBLANK(cat2Selected),ISBLANK(cat3Selected)),
sum(Stack[Amount]),
IF(and(not(ISBLANK(cat2Selected)), not(ISBLANK(cat3Selected))),
CALCULATE(SUM(Stack[Amount]),Stack[Category2] = cat2Selected && Stack[Category3]=cat3Selected),
IF(NOT(ISBLANK(cat2Selected)),
CALCULATE(sum(Stack[Amount]),Stack[Category2]=cat2Selected),
CALCULATE(sum(Stack[Amount]),Stack[Category3]=cat3Selected)
)
))
)
return cat1Total
Second(分母(用于计算所有类别的堆叠量
*Denominator = CALCULATE(SUM(Stack[Amount]),ALL(Stack[Category1]))*
&然后计算%,
*Percentage = DIVIDE(Stack[check msr],[normal all mSR],0)*
附上一些截图供您参考: