Totals of Matrix visual in custom column at end of visual

[siva_powerbi]

I have generated a Matrix visual, total column is added at end of visual by default but I need the total to be a custom column not the auto generated one.

Since I am generating 2 saperate tables and making a union out of it, so I need totals of two tables saperately.

Input Data

Date	Amount
03/01/20	100
04/01/20	50
05/01/20	70
06/01/20	150
07/01/20	170
08/01/20	120
09/01/20	90
10/01/20	80

Following formula I am using to generate columns:

Column 2 = SWITCH(TRUE(),

AND(Sheet2[Column1].[Date] >= DATE(2020,10,01) , Sheet2[Column1].[Date] < DATE(2020,11,01)),"Oct",

AND(Sheet2[Column1].[Date] >= DATE(2020,09,01) , Sheet2[Column1].[Date] < DATE(2020,10,01)),"Sep",

"Summary")  // Here I am inserting the new column summary for total of months (Sep, Oct)

Formual to generate the values

I tried two formulas one to generate the value of summary in else part as below (But this didn't work)

Column =

SWITCH(TRUE(),

AND(Sheet2[Column1].[Date] >= DATE(2020,10,01) , Sheet2[Column1].[Date] < DATE(2020,11,01)),Sheet2[Column2],

AND(Sheet2[Column1].[Date] >= DATE(2020,09,01) , Sheet2[Column1].[Date] < DATE(2020,10,01)),Sheet2[Column2],

CALCULATE(SUM(Sheet2[Column2]),(Sheet2[Column1].[Date] >= DATE(2020,10,01) && Sheet2[Column1].[Date] <

DATE(2020,11,01))))

Second Appraoch created a measure

Measure 2 = IF ( COUNTROWS ( DISTINCT ( Sheet2[Column 2]) ) = 1 , SUM ( Sheet2[Column2] ) ,

CALCULATE(SUMX(Sheet2, Sheet2[Column2]), Sheet2[Column 2] in {"Oct"}))

Visual

Here  I am getting data in summary but its a wrong value, I need summary to be 170 instead of 660.

I am sure their should be a way.

Thanks in advance for help.

[V-lianl-msft]

Hi @siva_powerbi ,

Based on your description, the column2 you created does not meet your calculation requirements.

The summary field is at the same level as the month field, so the summary field does not calculate the sum of "OCT" and "SEP".

You need to use the all function to clear the filters in the table.

Try to create measure like this:

Measure = 
var summary = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Column 2]in {"oct","sep"}))
var in_month = SUM('Table'[Amount])
return IF(MAX('Table'[Column 2])="summary",summary,in_month)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[siva_powerbi]

@V-lianl-msft Thank you very much for the solution, its working.

Can you please help me with one more functionality.

In matrix visual that I have, their are rows that expand up to 5 levels, iwith your formual I am getting grand totals at all levels but I need the total to be changing as the level is expanded that is sum should be correct as the level is expanded or colapsed.

Can you please help me.

Thanks

[amitchandak]

@siva_powerbi , Not very clear on the approach, You can create measures with help from time intelligence and date table like 

//This month (Oct selected in slicer) and last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))