DAX计算欧盟各国仇外群体性别占比异常及全样本占比求解

一、现有度量值异常原因 #

• 分母范围错误：COUNTROWS(ALLNOBLANKROW('male-female xenophobes'))返回的是整个数据集的总样本数，而非当前筛选的该国样本数，导致计算结果是仇外人群占全欧盟的比例，而非该国样本内的占比，这是图表异常的核心原因。
• 数据类型不匹配：如果[gndr]列是数字类型，用字符串"1"/"2"匹配会导致筛选失效，统计结果为0或不准确。
• 冗余计算：COUNTAX可以简化为COUNTROWS，逻辑更清晰直观。

二、修正后的各国仇外男女占比DAX #

1. 仇外男性/女性计数度量值 #

MeasureMale = 
CALCULATE(
    COUNTROWS('male-female xenophobes'),
    'male-female xenophobes'[gndr] = 1, -- 若gndr是文本类型则改为"1"
    'male-female xenophobes'[impcntr] = 4,
    'male-female xenophobes'[imsmetn] = 4,
    'male-female xenophobes'[imdfetn] = 4
)

MeasureFemale = 
CALCULATE(
    COUNTROWS('male-female xenophobes'),
    'male-female xenophobes'[gndr] = 2, -- 若gndr是文本类型则改为"2"
    'male-female xenophobes'[impcntr] = 4,
    'male-female xenophobes'[imsmetn] = 4,
    'male-female xenophobes'[imdfetn] = 4
)

2. 各国样本内占比度量值 #

percentage xenM per country = 
100 * DIVIDE(
    [MeasureMale],
    CALCULATE(COUNTROWS('male-female xenophobes'), ALLEXCEPT('male-female xenophobes', 'male-female xenophobes'[国家列名])), -- 替换为你的国家列实际名称
    0 -- 处理分母为0的异常情况
)

percentage xenF per country = 
100 * DIVIDE(
    [MeasureFemale],
    CALCULATE(COUNTROWS('male-female xenophobes'), ALLEXCEPT('male-female xenophobes', 'male-female xenophobes'[国家列名])), -- 替换为你的国家列实际名称
    0
)

注意：将代码中的[国家列名]替换为数据集中存储国家名称/代码的列（如country），ALLEXCEPT会保留当前国家的筛选上下文，确保分母是该国的总样本数。

三、全欧盟样本仇外男女占比DAX #

1. 全欧盟仇外男女计数 #

EU_Male_Xenophobes = 
CALCULATE(
    COUNTROWS('male-female xenophobes'),
    ALL('male-female xenophobes'[国家列名]), -- 忽略国家筛选，计算全欧盟范围
    'male-female xenophobes'[gndr] = 1,
    'male-female xenophobes'[impcntr] = 4,
    'male-female xenophobes'[imsmetn] = 4,
    'male-female xenophobes'[imdfetn] = 4
)

EU_Female_Xenophobes = 
CALCULATE(
    COUNTROWS('male-female xenophobes'),
    ALL('male-female xenophobes'[国家列名]),
    'male-female xenophobes'[gndr] = 2,
    'male-female xenophobes'[impcntr] = 4,
    'male-female xenophobes'[imsmetn] = 4,
    'male-female xenophobes'[imdfetn] = 4
)

2. 全欧盟占比计算 #

EU_Percentage_XenM = 
100 * DIVIDE(
    [EU_Male_Xenophobes],
    CALCULATE(COUNTROWS('male-female xenophobes'), ALL('male-female xenophobes'[国家列名])), -- 全欧盟总样本数
    0
)

EU_Percentage_XenF = 
100 * DIVIDE(
    [EU_Female_Xenophobes],
    CALCULATE(COUNTROWS('male-female xenophobes'), ALL('male-female xenophobes'[国家列名])),
    0
)

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