powerbi - PowerBI 百分比计算

Question

Team   ID   FRUIT
Team1  ID1  apple
Team1  ID2  apple
Team1  ID4  apple
Team1  ID5  apple
Team1  ID6  apple
Team1  ID7  apple
Team1  ID9  apple
Team2  ID11 apple
Team2  ID13 apple
Team2  ID14 apple
Team2  ID15 apple
Team2  ID17 apple
Team1  ID1  pear
Team1  ID4  pear
Team1  ID5  pear
Team1  ID8  pear
Team2  ID13 pear
Team2  ID15 pear
Team2  ID16 pear
Team2  ID17 pear
Team2  ID18 pear
Team2  ID19 pear
Team2  ID20 pear
Team1  ID1  strawberry
Team1  ID5  strawberry
Team2  ID20 strawberry
Team1  ID3  
Team1  ID7
Team1  ID9
Team1  ID10
Team2  ID12

我尝试在 PowerBI 中计算：在每个 TEAM 中，不同 ID 的百分比具有每种类型的 FRUIT。（需要保留 FRUIT 变量为空白的行，因为某些 ID 没有 FRUIT 数据，但它们需要计入分母）

我的预期结果是：

Team1  apple       70%
Team2  apple       50%
Team1  pear        40%
Team2  pear        70%
Team1  strawberry  20%
Team2  strawberry  10%
Team1              40%
Team2              10%

我使用了以下措施，但没有奏效，有人可以帮忙吗？谢谢。

Measure = 
CALCULATE(
    DISTINCTCOUNT(Sheet1[ID]), 
    GROUPBY(
        Sheet1, 
        Sheet1[FRUIT]
    )
)/
CALCULATE(
    DISTINCTCOUNT(Sheet1[ID]), 
    GROUPBY(
        Sheet1, 
        Sheet1[TEAM]
    )
)

标签： powerbipercentage

Answer 1

您可以根据自己的目的尝试以下措施。只需使用菜单栏将测量类型更改为 % -

team_wise_percentage = 

VAR total_distinct_id = 
CALCULATE(
    DISTINCTCOUNT(Sheet1[ID]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Team] = MIN(Sheet1[Team])
    )
)

VAR total_distinct_id_for_current_row_fruit = 
CALCULATE(
    DISTINCTCOUNT(Sheet1[ID]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Team] = MIN(Sheet1[Team])
            && Sheet1[FRUIT] = MIN(Sheet1[FRUIT])
    )
)

RETURN total_distinct_id_for_current_row_fruit/total_distinct_id

这是最终的输出 -