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

[afaherty]

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 ID	Test	Subtest	Points	Max
1	Math	Fractions	5	7
1	Math	Equations	7	7
1	Math	Decimals	3	6
1	English	Parts of speech	8	12
1	English	Diagramming	11	13
1	English	Conjugations	9	10

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.

[Ashish_Mathur]

Hi,

Share the download link of the PBi file.

[Irwan · Accepted solution]

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]

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