Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Breaking down the normal distribution by attributes

Hey,

 

We have just conducted a survey and I'm looking to understand if the respondents who answered the survey are representitive of our population as a whole. Mainly, are we getting the same proportion of learner in our sample as in the population at large

I want to conduct a two tailed Significance Test for a Proportion. I have started by looking to lift the Norm.Dist() formula from Excel.. but I can't get it to break down by sector

for the left tailed hypothesis 

NORM.DIST([Percentage of respondents by sector],[Percentage in population by sector],COUNTROWS(ALLSELECTED('Learner Experience Baseline data')),1)


But when drop this measure in a table, all sectors return the same result.

Any ideas?

 

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    First of all, your model relationship may be a bit problematic, it should be like this:

    Secondly, the results returned by your two percentage measures may have problems, please refer to the following formula:

    Percent of respondents = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT('Sample'[External Data Reference]),
    ALLEXCEPT(
        'Sample',
        'Sample'[Sector]
    )
)
VAR y = 
CALCULATE(
    DISTINCTCOUNT('Sample'[External Data Reference]),
    ALL('Sample')
)
RETURN
DIVIDE(
    x,
    y,
    BLANK()
)

Percent of total Population = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT(Population[ID]),
    ALLEXCEPT(
        'Population',
        'Population'[Sector]
    )
)
VAR y = 
CALCULATE(
    DISTINCTCOUNT(Population[ID]),
    ALLSELECTED('Sample'[Sector])
)
RETURN
 DIVIDE(
    x,
    y,
    BLANK()
) 
    Left_Tailed_Hypothesis = 
NORM.DIST(
    [Percent of respondents], 
    [Percent of total Population],
    1,
    TRUE()
)

    The value of COUNTROWS(ALLSELECTED('Sample')) is too big, which is not suitable for Standard_dev parameters.
    And its value is equal to a fixed value 899.
     
    You can refer to https://docs.microsoft.com/en-us/dax/norm-dist-dax to learn NORM.DIST() function.
     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

9 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi, Anonymous 

     

    I assume [Percentage of respondents by sector] and [Percentage in population by sector] are measures. Could provide a screeen shot of a table visual with the sectors on the rows, with the measures and the norm distribution as values?

     

    What are you do want to achieve with the ALLSELECTED-statement? The ALLSELECTED is a tricky function, and sometimes yields unexpected results.

     

    Cheers,
    Sturla

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hey sturlaws ,

       

      thanks for the response and your help, the [Percentage of...] are measures and yes I am using an ALLSELECTED(). I have posted the formulas and table below

       

      Percentage of respondents = DIVIDE(
      [Number respondents],
      CALCULATE(
      [Number respondents],
      ALLSELECTED('sample data')))

       

      Percentage of total Population = divide([Number in Population],CALCULATE([Number in Population],ALLSELECTED('Population Data')))
       
      Number respondents = DISTINCTCOUNT('Sample data'[Response ID])
       
      Number in Population = DISTINCTCOUNT('Population Data'[ID])
       
       
       

       

      • sturlaws's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Sorry, really bad spelling on my behalf in my previous post. What I meant to ask was, why have you included ALLSELECTED in your measures?

        I created a sample report, and I can't replicate the behaviour you are experiencing. Could you share your report, or create sample report where you reproduce the behaviour you are experiencing?

         

        Cheers,
        Sturla

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    As sturlaws  said, we may need the formulas of [Percentage of respondents by sector] and [Percentage in population by sector] to research the problem.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.