Help with Grouping totals as a measure need help

[Anonymous]

Hi there, 

I have the headers as per below. I would look to create a measure that calculates the total number of responses per Classroom session ID. I would like the classroom ID 454004 to provide the total 2? Any ideas would be appreciated... I am absolutely stumped.

User ID	Classroom Session ID Number	Sum of Responses
c777510	441192	1
c777510	441193	1
c777510	441195	1
cm05d8x	444282	1
cm05gws	444336	1
cm02s13	444337	1
cm044b1	444340	1
cm044b1	444345	1
cm05gws	444346	1
cm044b1	444486	1
cm05gws	444489	1
cm044b1	444495	1
cm044b1	444500	1
cm044b1	444507	1
cm03fx7	444534	1
cm09z15	444592	1
cm07f0f	446685	1
c597177	454004	1
cm01o64	454004	1

[Anonymous]

Basically I wanted to group the total count of User IDs, that attended each classroom session. Sorry if I am not explaining this very well.

[PaulDBrown · Accepted solution]

Try:

Count =
CALCULATE (
    SUM ( Table[Sum of Responses] ),
    ALLEXCEPT ( Table, Table[Classroom ID] )
)

[Anonymous]

Thanks 🙂 This did the job :)!!!

[PaulDBrown]

Please mark the appropriate answer as the solution to close th thread and helps others searching for similar solutions. Thanks!

[GzzztBrain]

Try:

MyMeasure = countax(filter(MyTable, [Classroom]=454004),[Classroom])

You can then drop the measure onto the page and it will show the value, 2.

[Anonymous]

Thank you GzzztBrain for the response. No joy. I am trying to find the number or occurances appear of the classroom ID. that one classroom I required 2 was just an example of the total I am expecting.

I really appreciate the help 🙂

[GzzztBrain]

In that case you don't want a measure. Drop a Matrix onto the page instead. Pop the Classroom into both the Rows and the Values, and make sure the latter is set to Count.

You can also do it with a Table, where you just drop the Classroom into the Columns twice, and set the second one to Count. Same result but I like to think the Matrix is more intuitive and allows you to add a second dimension (columns) if you like. 

[Anonymous]

Annoyingly I do not use visuals for Power BI. I need to do all calculations in Power BI and then export the table.  The visual element and editing is quite straight forward. I just need all the different tables to talk to each other.

[GzzztBrain]

Hmm... I may not have sufficient expertise to address the question then, but if I were in your shoes I'd use the visual anyway. You can export the data from the visual.

[Anonymous]

Thank you very much for your time GzzztBrain This will have to be my work around for now.