DAX statistics calculation

Question

The problem is solved! Sam Nseir has found a solution!

[I am currently analyzing a survey (approx.60.0000 respondents in 30 European countries) on the topic of xenophobia. More precisely: how many women and men in each country (in % of the sample) are xenophobic.

Ive checked a bit of this data by Czech Republic (CZ in the table - out of 2476 respondents from CZ 574 have answered xenophobic questions (3 times NO to migrants, i.e. 4-4-4 in the answers (column names [impcntr], [imsmetn], [imdfetn])) -and these are 259 men and 315 women (i.e. of all respondents in the Czech Republic, so 10.46% and 12.72% female out if 2476). This is exactly what I need for each country in Europe (the percentages).

Also --the gender is mentioned as -1 male and 2-female from the [gndr] column.

I’ve made these formulas in the first place:

MeasureMale= CALCULATE(COUNTAX('male-female xenophobes','male-female xenophobes'[gndr]), 'male-female xenophobes'[gndr]="1" && 'male-female xenophobes'[impcntr]=4 && 'male-female xenophobes'[imsmetn]=4 && 'male-female xenophobes'[imdfetn]=4)

MeasureFemale = CALCULATE(COUNTAX('male-female xenophobes','male-female xenophobes'[gndr]), 'male-female xenophobes'[gndr]="2" && 'male-female xenophobes'[impcntr]=4 && 'male-female xenophobes'[imsmetn]=4 && 'male-female xenophobes'[imdfetn]=4)

Then I’ve made some New Measures to calculate a percentage: how many men out of each country are xenophobic and how many women (in % out of samples per country).

percentage xenM per country = 100*divide([MeasureMale], COUNTROWS(ALLNOBLANKROW('male-female xenophobes')))

percentage xenF per country = 100*divide([MeasureFemale], COUNTROWS(ALLNOBLANKROW('male-female xenophobes')))

However, the resulting graph seems incorrect.. what could be the reason, maybe someone can advise??

Further I need to calculate how many man and women (in %) out of Entire EU sample from my datasheet (approx.60.000) is xenophobic. Could anyone help me here?

Thank you a lot in advance!!

Answer 1

I think I have spotted two issues:

1. Your are multiplying your percentage by 100 (you shouldn't)
2. ALLNONBLANKROW returns the whole table ie 60,000

Put these together, and it could explain your chart. For example:

• 259 CZ men out of 60,000 = 0.0043
• your a multiplying that by 100 = 0.43
• and most likely you have the measure formatted to Percentage = 43%

43% looks pretty close to what you have on your chart for CZ Male.

---

Try the following (these are all measures):

Respondent Count = COUNT('male-female xenophobes'[idno])

MeasureMale= CALCULATE(
    [Respondent Count],
    'male-female xenophobes'[gndr] = "1" &&
    'male-female xenophobes'[impcntr] = 4 &&
    'male-female xenophobes'[imsmetn] = 4 &&
    'male-female xenophobes'[imdfetn] = 4
  )

MeasureFemale = CALCULATE(
    [Respondent Count],
    'male-female xenophobes'[gndr] = "2" &&
    'male-female xenophobes'[impcntr] = 4 &&
    'male-female xenophobes'[imsmetn] = 4 &&
    'male-female xenophobes'[imdfetn] = 4
  )


percentage xenM = DIVIDE( [MeasureMale], [Respondent Count])
percentage xenF = DIVIDE( [MeasureFemale], [Respondent Count])

percentage xenM of All = DIVIDE(
    [MeasureMale],
    CALCULATE( [Respondent Count], ALL('male-female xenophobes') )
  )

percentage xenF of All = DIVIDE(
    [MeasureFemale],
    CALCULATE( [Respondent Count], ALL('male-female xenophobes') )
  )