topic Re: calculating column total of a matrix through DAX in DAX Commands and Tips

calculating column total of a matrix through DAX

Anonymous — Wed, 06 May 2020 06:50:46 GMT

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%

Re: calculating column total of a matrix through DAX

amitchandak — Wed, 06 May 2020 07:41:32 GMT

Anonymous , using isfiltered or has onevalue change the grand total formula.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

So, create one more formula that will give correct grand total that should be used in else like

if(isfiltered(Table[Group],[Room Night],[Room Night Total])

You will not get two totals as the hybrid display is not supported

I logged idea for that

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table-matrix-and-table-together

If your date table is proper, that you do not need all(date[date]) with time intelligence

Re: calculating column total of a matrix through DAX

Anonymous — Thu, 07 May 2020 05:21:47 GMT

amitchandak

Thanks for your sloution , i create one new measure using ur suggested formula like

Measure = if(ISFILTERED(BOOKINGCHANNEL[BOOKING_CHANNEL]);[Room Nights]) and total column is gets empty and i create another formula for displying grand total based on condition .but still i am struggling with how to incorporate my grand total formula into the matrix ..please help me .because its my urgent requirement and i am new to powerBI.

Re: calculating column total of a matrix through DAX

amitchandak — Thu, 07 May 2020 05:24:43 GMT

Anonymous , Data you have shared, is it your Raw Data? if not please share a sample. with the expected output. I will try.

Re: calculating column total of a matrix through DAX

Anonymous — Thu, 07 May 2020 06:10:10 GMT

amitchandak

The table which i shown in the question is my sample data .i dont know how to upload a pbix file sample in community.please help me with the table which i given there. i mentioned all the details there with expected output.please help me???? i just need a grand total for particular condition . and need to incorporate in to the matrix

Re: calculating column total of a matrix through DAX

amitchandak — Thu, 07 May 2020 06:33:45 GMT

Anonymous , You can upload it to dropbox or one drive and share link