Forum Discussion
How to get Total in Table Matrix based using Calculated measure or column?
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:-
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 measureMeasure = 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.
7 Replies
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/
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
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]))))
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
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?
Please try this measure instead for your Total Occupied
Ratio Occupied = var occupied = SUM(Seats[OccupiedSeats])var totalseats = SUM(Seats[ TotalSeats])return occupied & "|" & totalseatsRegards,Pat
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 measureMeasure = 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.
