Forum Discussion

afaherty's avatar
afaherty
Icon for Helper V rankHelper V
2 years ago
Solved

Attempting to create a histogram (distribution) bar chart based on a measure

Hello,

I am attempting to create a histogram (distribution) bar chart based on a measure and am failing miserably. I wasn't sure if this question should go here or in the DAX forum.

 

Here is an example of my student test data:

 

Student IDTestSubtestPointsMax
1MathFractions57
1MathEquations77
1MathDecimals36
1EnglishParts of speech812
1EnglishDiagramming1113
1EnglishConjugations910

 

I have a measure called Score which gives each student's Math score and English score (depending on which is chosen via slicer): 

DIVIDE(SUM('Test Scores'[Points]),SUM('Test Scores'[Max]))

 

So in this case it would show in a matrix that student 1 got 15/20 = 75% on math and 28/35 = 80% on English.

 

The problem is that I need to see the class's distribution of scores, like for example this chart, which shows how many students scored each score:

 

 

However, this is not possible with a measure. Can anyone offer any solutions? Thank you.

Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    hello afaherty 

     

    please check if this accomodate your need.

     

    first, i assumed the percent only limited to 'Test' only. also i added another student ID because i assumed you will have plenty of different student id.

     

    1. create a measure as your measure above.

    Sum of Point = DIVIDE(SUM('Table'[Points]),SUM('Table'[Max]))
     
    2. create a calculated column with following DAX as you need for your x-axis value.
    Percent = 
    CALCULATE(
        [Sum of Point],
        FILTER(
            'Table',
            'Table'[Test]=EARLIER('Table'[Test])&&
            'Table'[Student ID]=EARLIER('Table'[Student ID])
        )
    )

     

    3. plot the column and student ID in bar chart (dont forget to change Y-axis into count distinct)

     

    4. to make similar to your example graphic, then change minimum and maximum value so it will show 0%-100% and invert range to make 100% on the left.

     

    Hope this will help.

    Thank you.

3 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    hello afaherty 

     

    please check if this accomodate your need.

     

    first, i assumed the percent only limited to 'Test' only. also i added another student ID because i assumed you will have plenty of different student id.

     

    1. create a measure as your measure above.

    Sum of Point = DIVIDE(SUM('Table'[Points]),SUM('Table'[Max]))
     
    2. create a calculated column with following DAX as you need for your x-axis value.
    Percent = 
    CALCULATE(
        [Sum of Point],
        FILTER(
            'Table',
            'Table'[Test]=EARLIER('Table'[Test])&&
            'Table'[Student ID]=EARLIER('Table'[Student ID])
        )
    )

     

    3. plot the column and student ID in bar chart (dont forget to change Y-axis into count distinct)

     

    4. to make similar to your example graphic, then change minimum and maximum value so it will show 0%-100% and invert range to make 100% on the left.

     

    Hope this will help.

    Thank you.

    • afaherty's avatar
      afaherty
      Icon for Helper V rankHelper V
      2 years ago

      Beautiful! Thank you so much, I really appreciate it!!