Show Total/Subtotal as difference between 2 Rows

[jackcoxer]

I've been asked this question by one of our reporting analysts and I can't seem to find anything online that gives me what i require.

Is there a way to show the bottom Row/Total/Subtotal of a table to show the difference between 2 rows? We've got the below table that shows the values of multiple measures for 2 locations. Is there a simple way of getting a final row to show the difference between each measure?

In excel, it would be a simple case of having =A1/A2 in the cells below the table as below - is there a way to get this within Power BI?

[Anonymous · Accepted solution]

Hi @jackcoxer 

You can refer to the following example

Sample data 

1.create a table.

Table 2 = var a=SELECTCOLUMNS({"Centre","Mobile","Key Gap"},"RowType",[Value])
var b=SELECTCOLUMNS({"Appt Time","Appt Location"},"ColumnType",[Value])
return GENERATE(a,b)

2.the measures are used.

Measure 2 = DIVIDE(COUNTROWS(FILTER('Table (3)',[Type]=MAX('Table 2'[RowType]))),COUNTROWS(ALL('Table (3)')))

Measure 3 = DIVIDE(SUMX(FILTER(ALL('Table (3)'),[Type]=MAX('Table 2'[RowType])),[Time]),SUMX(ALL('Table (3)'),[Time]))

3.Then create a measure to use switch() function to judge.

Measure = SWITCH(TRUE(),MAX('Table 2'[RowType])<>"Key Gap"&&MAX('Table 2'[ColumnType])="Appt Location",[Measure 2],MAX('Table 2'[ColumnType])="Appt Time"&&MAX('Table 2'[RowType])<>"Key Gap",[Measure 3],MAX('Table 2'[RowType])="Key Gap"&&MAX('Table 2'[ColumnType])="Appt Location",CALCULATE([Measure 2],'Table 2'[RowType]="Centre")-CALCULATE([Measure 2],'Table 2'[RowType]="Mobile"),MAX('Table 2'[RowType])="Key Gap"&&MAX('Table 2'[ColumnType])="Appt Time",CALCULATE([Measure 3],'Table 2'[RowType]="Centre")-CALCULATE([Measure 3],'Table 2'[RowType]="Mobile"))

Put the columns of table2 to a matrix visual and put the measure to the value field

The information above just a sample for you to offer, you can consider that use switch() function in a measure .

Best Regards!

Yolo Zhu

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