Forum Discussion
Show Total/Subtotal as difference between 2 Rows
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?
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.
1 Reply
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.