This is the matrix i am using in which ( Room Night , room Night last year , Room Night SWLY and Room Night %Diff )are the measures using which are shown in row side by Values section on the formatting pane and look for Show On rows and Toggle that switch to On. And that helps me to put 4 values on rows. But my problem is i need to calculate column total using DAX query .beacause for each measure in the row the grand total is different we cant use the default powerbi column total of matrix ..someone please help me this is my requirement
Here ABC ,DEF , DHI ,JKL ,MNO ARE are the booking channel .( comes from booking channel table booking channel column)
under values room night etc are calculated measures
Total is the default column total of powerbi matrix
but I NEED LIKE THIS grand total of room_night = sum of (ABC + DEF + DHI +JKL )
grand total of room_night LAST YEAR = sum of (ABC + DEF + DHI +JKL + MNO )
grand total of room_night SWLY = sum of (ABC + DEF + DHI +JKL )
grand total of Room Night %Diff = ((grand total of room_night SWLY- Room Night of ABC) /grand total of room_night SWLY) * 100
Meaures used in matirx:
Room Night = SUM(TABLE[TOTAL NIGHTS])
Room Night last year = CALCULATE([RoomNights];SAMEPERIODLASTYEAR(DATE1[DATE]);all(DATE1))
Room Night SWLY = CALCULATE([Room Night];DATEADD(DATE1[DATE];-364;DAY);ALL(DATE1[DATE]))
Room Night %Diff = DIVIDE(Room Night - Room Night last year;Room Night)
please help me!!!!!!😑
| Values | ABC | DEF | DHI | JKL | MNO | Total | Grand_Total |
| Room Night | 20 | 10 | 30 | 20 | 10 | 90 | 80 |
| room Night last year | 10 | 5 | 20 | 18 | 8 | 61 | 61 |
| Room Night SWLY | 9 | 6 | 19 | 17 | 6 | 57 | 51 |
| Room Night %Diff | 50% | 50% | 33.3% | 10% | 20% | 32.2% | 60% |
