Forum Discussion
Adding a custom total Row and Total Column for matrix
Hi sjain6,
Perhaps you can take a look at below formula if it suitable for your requirement:
Replace bold part with your calculate formula.
Total Calculate = IF(ISBLANK(MAX('Table'[Date]))=FALSE(), IF(COUNTROWS('Table')=COUNTROWS(ALL('Table')), "All",//all IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]), "Total Group",//filter with gorup IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[WeekDay]=MAX([WeekDay])),[Date]), "Total Column",//filter with column IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[WeekDay]=MAX([WeekDay])),[Date]), "Sub Column",//filter with group + column IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]), "Total Row",//filter with group + row SUM('Table'[Amount])))))))BTW, if you want hide some 'total level', you can use blank function to replace that part text.
Total Calculate = IF(ISBLANK(MAX('Table'[Date]))=FALSE(), IF(COUNTROWS('Table')=COUNTROWS(ALL('Table')), "All",//all IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]), BLANK(),//"Total Group",//filter with gorup IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[WeekDay]=MAX([WeekDay])),[Date]), "Total Column",//filter with column IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[WeekDay]=MAX([WeekDay])),[Date]), BLANK(),//"Sub Column",//filter with group + column IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]), "Total Row",//filter with group + row SUM('Table'[Amount])))))))Regards,
Xiaoxin Sheng
Hi sjain6,
Perhaps you can take a look at below formula if it suitable for your requirement:
Replace bold part with your calculate formula.
Total Calculate =
IF(ISBLANK(MAX('Table'[Date]))=FALSE(),
IF(COUNTROWS('Table')=COUNTROWS(ALL('Table')),
"All",//all
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
"Total Group",//filter with gorup
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[WeekDay]=MAX([WeekDay])),[Date]),
"Total Column",//filter with column
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[WeekDay]=MAX([WeekDay])),[Date]),
"Sub Column",//filter with group + column
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
"Total Row",//filter with group + row
SUM('Table'[Amount])))))))
BTW, if you want hide some 'total level', you can use blank function to replace that part text.
Total Calculate =
IF(ISBLANK(MAX('Table'[Date]))=FALSE(),
IF(COUNTROWS('Table')=COUNTROWS(ALL('Table')),
"All",//all
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
BLANK(),//"Total Group",//filter with gorup
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[WeekDay]=MAX([WeekDay])),[Date]),
"Total Column",//filter with column
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[WeekDay]=MAX([WeekDay])),[Date]),
BLANK(),//"Sub Column",//filter with group + column
IF(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
"Total Row",//filter with group + row
SUM('Table'[Amount])))))))
Regards,
Xiaoxin Sheng
Thank you for your input Anonymous
However, when I try to use this I get an issue. The part of the snippet that you highlighted in bold needs to have "Percentage" format and the rest of the data needs to be of type "whole number". Any idea how I can fix this.
Also, the if statements are needed but my final output seems to be a reflection of the last if condition. If possible, could you break down one of the if statements for me so that I have a better idea about it.
Thank you once again!
Regards,
SJ