Adding a custom total Row and Total Column for matrix

Hi all, I am a new user of PowerBI and I have been trying to create a matrix with some data. I can get the value part of the matrix correctly (with 1s and 0s), but I am unable to add a total row ...

Need Help

Anonymous

9 years ago

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

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])))))))

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])))))))

Forum Discussion

Adding a custom total Row and Total Column for matrix

Related content

Adding columns

Adding a column

Adding custom visual without data mapping

Renaming columns and adding columns

Adding a custom column to a copy activity