statisticsdax

DAX statistics calculation


The problem is solved! Sam Nseir has found a solution! enter image description here

[graph][1]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!!


Solution

  • 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') )
      )