PowerBI中如何批量筛选多文本列并计算平均百分比？

核心思路 #

把10个目标列转换成纵向虚拟表，一次性统计所有列的On-Site数量，再按需计算平均值和百分比，避免重复创建度量值。

场景1：计算所有列中On-Site的总平均占比 #

统计10列里On-Site的总次数，除以「列数×总记录数」，得到整体的平均百分比：

Avg On-Site Percentage = 
VAR TotalRows = COUNTROWS('Telework-NEW')
VAR ColumnCount = 10 // 替换为你的实际列数
VAR AllOnSiteCount = 
    SUMX(
        // 将10列转成纵向表，每行对应一列的一个值
        UNION(
            SELECTCOLUMNS('Telework-NEW', "Value", [MON]),
            SELECTCOLUMNS('Telework-NEW', "Value", [TUE]),
            SELECTCOLUMNS('Telework-NEW', "Value", [WED]),
            SELECTCOLUMNS('Telework-NEW', "Value", [THU]),
            SELECTCOLUMNS('Telework-NEW', "Value", [FRI]),
            SELECTCOLUMNS('Telework-NEW', "Value", [SAT]),
            SELECTCOLUMNS('Telework-NEW', "Value", [SUN]),
            // 补充剩余3列，示例为[COL8]/[COL9]/[COL10]，替换成你的实际列名
            SELECTCOLUMNS('Telework-NEW', "Value", [COL8]),
            SELECTCOLUMNS('Telework-NEW', "Value", [COL9]),
            SELECTCOLUMNS('Telework-NEW', "Value", [COL10])
        ),
        IF([Value] = "On-Site", 1, 0)
    )
RETURN
    DIVIDE(AllOnSiteCount, TotalRows * ColumnCount, 0)

场景2：计算单个列On-Site占比的平均值 #

先单独计算每一列的On-Site占比（该列On-Site数/总行数），再求10个占比的平均值：

Avg Column On-Site Percentage = 
VAR TotalRows = COUNTROWS('Telework-NEW')
VAR ColumnOnSiteRatios = 
    {
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[MON]), 'Telework-NEW'[MON] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[TUE]), 'Telework-NEW'[TUE] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[WED]), 'Telework-NEW'[WED] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[THU]), 'Telework-NEW'[THU] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[FRI]), 'Telework-NEW'[FRI] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[SAT]), 'Telework-NEW'[SAT] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[SUN]), 'Telework-NEW'[SUN] = "On-Site"), TotalRows, 0),
        // 补充剩余3列的占比计算，替换成你的实际列名
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[COL8]), 'Telework-NEW'[COL8] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[COL9]), 'Telework-NEW'[COL9] = "On-Site"), TotalRows, 0),
        DIVIDE(CALCULATE(COUNT('Telework-NEW'[COL10]), 'Telework-NEW'[COL10] = "On-Site"), TotalRows, 0)
    }
RETURN
    AVERAGEX(ColumnOnSiteRatios, [Value])

卡片格式设置 #

将创建好的度量值拖入卡片视觉对象，在「格式」面板的「数据标签」中，设置「显示单位」为百分比，调整小数位数即可。

内容的提问来源于stack exchange，提问作者drcoz