Sum and display columns based on slicer

[jrmaier]

I work at a high school using a hybrid schedule. Some students in on A days and others in on B days. Some parents want their kids at school everyday (A-S, B-S designations on their record) I need to figure out how many A hybrid and B student kids are in each class to see if we have enough safe room for all in the classroom or if we need to have some students watching the class on Zoom from somewhere else in the building.

Trying to have A-H # and B-S # show and sum when A is selected on the Group slicer and the same for B-H# and A-S# when Group B is selected. 

Currently both A's show up when group A is selected and same for B because I originally created new columns using "Group" & "Attend" 

[jrmaier · Accepted solution]

@tex628 Awesome. Thanks for your help! 

[Greg_Deckler]

@jrmaier - I'm not clear on this. So you did merge them so that you have a single column of B-S, B-H, A-S, A-H, ?? If so I think you would be home free, not sure why you need individual columns for those, just create a matrix visualization, put that column in for your Columns and do a simple count. But, I don't fully understand your data I think.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jrmaier]

@Greg_Deckler 

Hi Greg,

Thanks for your reply and help. I am a First-time poster so I appreciate the postings tips very much.

I made the matrix display. Here is the data for one class. We alternate "A" days and "B" days every other school day. I need a sum of a couple of types of students depending on which day it is in order to determine if we need to find an overflow area for students to Zoom the class while at school because the classroom can only fit so many students due to social distancing practices.

• A-H students attend only on A days and Zoom from home on B days.
• A-S students attend on A days and B days, but possibly not in the classroom on B days due to desk limits.
• The B-H and B-S students follow the above schedules for B days.
• C students learn from home every day.

Room	Term	Course	Teacher	Code	Count of Student
117	Fall Semester	AP Calculus AB*	BD	A-H	5
117	Fall Semester	AP Calculus AB*	BD	A-S	6
117	Fall Semester	AP Calculus AB*	BD	B-	1
117	Fall Semester	AP Calculus AB*	BD	B-H	1
117	Fall Semester	AP Calculus AB*	BD	B-S	11
117	Fall Semester	AP Calculus AB*	BD	C-R	1

When it is an "A" day the following students are at the school. A-H (5) + B-S students (11). The count column should equal 16.

On a "B" day it is B-H + A-S students.

C-R students have chosen to learn remotely every day so they do not need to be counted.

Here is an image of the table headers if that helps. (Seems like you can't export a table?)

This is what the matrix looks like when I select "A" on the day slicer

I need to have B-S to populate, instead of  A-H.

Thanks in advance for a reply.

Jim

[v-deddai1-msft]

Hi @jrmaier ，

Did your code column contains A-S, A-H,B-S,B-H,C-R? If so you can use the following measure to calculate number students attended.

First create a calculated table for slicer :

Table2 = VALUES(Table[Code])

Then you can create following measure  for your matrix:

measure =
SWITCH (
    SELECTEDVALUE ( Table2[Code] ),
    "A",
        IF (
            HASONEVALUE ( Table[Code] ),
            IF (
                MAX ( Table[Code] ) IN { "A-H", "B-S" },
                CALCULATE (
                    COUNT ( Table[UserID] ),
                    FILTER (
                        ALLEXCEPT ( Table, Table[Room], Table[Term], Table[Course] ),
                        Table[Code] = MAX ( Table[Code] )
                    )
                ),
                BLANK ()
            ),
            CALCULATE (
                COUNT ( Table[UserID] ),
                FILTER (
                    ALLEXCEPT ( Table, Table[Room], Table[Term], Table[Course] ),
                    Table[Code] IN { "A-H", "B-S" }
                )
            )
        ),
    "B",
        IF (
            HASONEVALUE ( Table[Code] ),
            IF (
                MAX ( Table[Code] ) IN { "A-S", "B-H" },
                CALCULATE (
                    COUNT ( Table[UserID] ),
                    FILTER (
                        ALLEXCEPT ( Table, Table[Room], Table[Term], Table[Course] ),
                        Table[Code] = MAX ( Table[Code] )
                    )
                ),
                BLANK ()
            ),
            CALCULATE (
                COUNT ( Table[UserID] ),
                FILTER (
                    ALLEXCEPT ( Table, Table[Room], Table[Term], Table[Course] ),
                    Table[Code] IN { "A-S", "B-H" }
                )
            )
        ),
    "C",
        IF (
            MAX ( Table[Code] ) = "C-R",
            CALCULATE (
                COUNT ( Table[UserID] ),
                FILTER (
                    ALLEXCEPT ( Table, Table[Room], Table[Term], Table[Course] ),
                    Table[Code] = MAX ( Table[Code] )
                )
            ),
            BLANK ()
        )
)

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

Best Regards,

Dedmon Dai

[jrmaier]

Hi Dedmon,

Thanks so much for your time and talent. I built the table and measure. Not sure how to place it in a matrix.

Jim

[jrmaier]

@v-deddai1-msft 

Hi Dedmon,

Thanks so much for your time and talent. I built the table and measure. Not sure how to place it in a matrix.

Jim

[v-deddai1-msft]

Hi @jrmaier ,

Put the column in the calculated table as slicer. And put the measure  in Value in matrix.

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

Best Regards,

Dedmon Dai

[jrmaier]

@v-deddai1-msft 

I wasn't actually sure which table to create the measure in so I made one in both. When I add either measure to the value field things disappear. Video of what is happening. https://www.screencast.com/t/UHhhFLStUyD

[v-deddai1-msft]

Hi @jrmaier ,

Have you create the slicer table for slicer like what I have post above? It is better that you could provide us sample pbix file.

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

Best Regards,

Dedmon Dai

[jrmaier]

@v-deddai1-msft Matrix calculation.pbix

https://drive.google.com/file/d/1OmIgLSNyRdASbyeRuL35cvNJdIp7BWFR/view?usp=sharing

Let me know if you need to Excel data file to go with that. Thanks for your continued help!

[v-deddai1-msft]

Hi @jrmaier ,

Check the permission of your shared file, I can't download it.

Best Regards,

Dedmon Dai

[jrmaier]

@v-deddai1-msft sorry about that. Please try again.

[tex628]

Take a look at the attached file. Let me know if it works out!

/ J

[jrmaier]

@tex628 Can you help me add a column? I need to subtract the Total column from the Data(Capacity) column for each row. If the answer will produce a negative number I would like it to show zero instead if possible. 

 

[tex628]

Hello @jrmaier , 

In the Demo file there are no values for capacity but I've attached a file with what I think you're aiming for. Take a look and let me know how it looks. There are a few more components this time and it's a little bit more complicated, so let me know if you need help implementing it. 

Br,
J

[jrmaier]

@tex628 I wasn't able to figure it out with the missing data. Can I email you the pbix I actually need worked on?

Thanks for your time and expert knowledge.

Jim

[tex628]

I've sent you a direct message. 

/ J

[jrmaier]

Thanks! Received and replied. 

[tex628]

I have yet to recieve anything, did you send an email or a PM? 

/ J

[jrmaier]

@tex628 I sent it last Friday. I just resent it.

[jrmaier]

@tex628 It looks great! How would I go about editing in order to make a new group "C"?

That is for the "full-time" remote students.