How to get Total in Table Matrix based using Calculated measure or column?

[deb_power123]

Hi,

In my input data I have the following columns: Date, TotalSeats,OccupiedSeats,SchoolName and Buildings.

 I want to show the  (sum of Occupied Seats/sum of Total Seats )per school and its percentageas highlighted in yellow color 'Total Occupied' and '%Occupied' in the expected output below.

Can someone suggest any DAX to achieve this?Can we handle that using calculated DAX measure or column? Please provide your suggestions.

[ P.S: ROW TOTAL and GRAND TOTAL properties doesnt serve the purpose here, they will simply add  and sum up all the %ge occupied seats so in that case it can be more than 100% if its simple additon if %Ocupied seats is aound 95% or more each building so we want sum TotalSeats/sum ocupied seats per building.]

My Input sample data :-

Date	TotalSeats	OccupiedSeats	School Name	Buildings
21-03-2021	10	1	School1	Building1
22-03-2021	10	4	School1	Building1
23-03-2021	10	4	School1	Building1
24-03-2021	10	1	School1	Building1
25-03-2021	10	2	School1	Building1
26-03-2021	10	1	School1	Building1
27-03-2021	10	1	School1	Building1
21-03-2021	12	5	School1	Building2
22-03-2021	12	8	School1	Building2
23-03-2021	12	8	School1	Building2
24-03-2021	12	3	School1	Building2
25-03-2021	12	4	School1	Building2
26-03-2021	12	2	School1	Building2
27-03-2021	12	4	School1	Building2

In my visualization, I can show every of the below table matrix except the one higlighted in yellow.

Expected Output should look like this in table matrix:-

[v-yuaj-msft · Accepted solution]

Hi @deb_power123 ,

Based on your description, I did a test. Is the following result what you want?

Here are the steps.

1. create a calculated column

Column = IF(RIGHT([Buildings],1)="1",[OccupiedSeats],[OccupiedSeats]-MINX(FILTER(ALL('Test_sum'),[Date]=EARLIER('Test_sum'[Date])),[OccupiedSeats]))

2. create a measure

Measure = DIVIDE(SUM('Test_sum'[Column]),SUM('Test_sum'[ TotalSeats]))

3. create a matrix.

4. set "grand total" in Format pane.

Hope that's what you were looking for.

Best Regards,

Yuna

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

[mahoneypat]

You can use this measure expression to get both your individual values and the correct values in the total rows.  Use it in a matrix or table with the School and Building columns.

Pct Occupied =
AVERAGEX (
    SUMMARIZE ( Seats, Seats[Buildings], Seats[School Name] ),
    CALCULATE (
        DIVIDE ( SUM ( Seats[OccupiedSeats] ), SUM ( Seats[  TotalSeats] ) )
    )
)

Regards,

Pat

[deb_power123]

Hi @mahoneypat The only issue i am facing is that using DAX it is not showing one Total row but each of the  buildings has its total row for %occupancy and also the ratio format is replaced with decimal format.I wanted to show in ratio  format using concatenation function.

The one below in red color is expected result and format .I want to show in ratio format but the DAX you suggested shows the results in decimal format and it shows total not in one row but under each building row like for Building1 and Building2 we have seperate Total instead of one total.Could you please suggest?

[mahoneypat]

Please try this measure instead for your Total Occupied

Ratio Occupied = var occupied = SUM(Seats[OccupiedSeats])

var totalseats = SUM(Seats[ TotalSeats])

return occupied & "|" & totalseats

Regards,

Pat

[amitchandak]

@deb_power123 , You can create two measures occupied and occupied % and in matrix, you can use the option "Show on row"

I am not sure you can get exact same output

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[deb_power123]

I didnt get you, which two measures you are stating here.Can you please elaborate? I need some DAX measure to be created to get the  (sum of Occupied Seats/sum of Total Seats )per school.

I am not sure how to achieve this DAX, could you guide

[amitchandak]

@deb_power123 , Try like

divide(sum(Table[OccupiedSeats]), calculate(sum(Table[OccupiedSeats]), filter(allselected(Table), Table[School Name] = max(Table[School Name ]) && Table[Buildings] = max(Table[Buildings]))))