Histogram showing count of a measure

[dphillips]

I have a table which has all the classes a student does and the number of units each of those subjects is. See screenshot. I have created a measure which sums up each of the students units.

Sum of Units = CALCULATE(
sum(uvNCStudentCurrentPastClassesAll[BOSUnits])
)

In the table shown, the first student has 10 units in total while the second student has 11 etc.

I would like to create a histogram showing how many student are on 10 units, how many on 11 units etc. I would like my X axis to have the following categories - Les than 10 Units, 10 Units, 11 Units, 12 Units, 13 Units, 14 Units, More than 14 units. 

How would I go about creating a histogram which shows a count of the total number of units each student has using the catgegories described above?

Thanks in advance for any help.

[v-shex-msft]

Hi @dphillips,

You can add a calculated column to calculate the category, then use it as the row label of matrix visual.

Total Unit =
VAR _count =
    SUMX (
        FILTER (
            ALL ( 'Number of Units' ),
            [Fileyear] = EARLIER ( [Fileyear] )
                && [Studentid] = EARLIER ( [Studentid] )
        ),
        [BOSUnits]
    )
RETURN
    IF ( _count < 10, "less 10", IF ( _count > 14, "Over 14", _count & "" ) )

Notice: you can also add more conditions to formula to make calculation more accurate.

Regards,

Xiaoxin Sheng

[dphillips]

Thanks - can you have a look at my formula? Seems to be incorrect results coming out.

Also - In the end I want a count of the number of students on, say, 11 units and use this in a bar graph as a histogram.

[v-shex-msft]

HI @dphillips,

>>Also - In the end I want a count of the number of students on, say, 11 units and use this in a bar graph as a histogram.

Can you provide the sample for test?

Regards,

Xiaoxin Sheng

[dphillips]

Hello Xiaoxin Sheng, the data is exactly the same as I sent you before. I want to total up all the units for each student but then do a count of these total units to get a histogram. Just use the link I sent you earlier.

Thanks for your help.

---

---

[v-shex-msft · Accepted solution]

HI @dphillips,

If you not want to set limit range category, you can try to use below formula:

Total Unit = 
VAR _count =
    SUMX (
        FILTER (
            ALL ( 'Number of Units' ),
            [Fileyear] = EARLIER ( [Fileyear] )
                && [Studentid] = EARLIER ( [Studentid] )
        ),
        [BOSUnits]
    )
RETURN
    IF ( _count < 10, "less 10", _count & " Units" )

BTW, I check on your formula and found some conditions seems conflict.

Notice: I also attach the pbix file.

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @dphillips,

Can you please share some sample data for test?

Regards

Xiaoxin Sheng

[dphillips]

Here is a link to the data on Dropbox

https://www.dropbox.com/s/5xdnycn0dqba9hv/Count%20of%20BOS%20Units.xlsx?dl=0

Note this data is across a number of fileyears and filesemesters. Would love to be able to look at a specific fileyear and semester but also be able to see any changes across all fileyears and semesters.

Basically, I want a count of the number of students on 1. Less than 10 units 2. 10 units 3. 11 Units 4. 12 Units 5. 13 Units 6. 14 Units 7. More than 14 Units.

Thanks for your help