topic How to calculate grand difference (similar to grand total) in matrix view in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-grand-difference-similar-to-grand-total-in/m-p/3872609#M151279 <P>Hi Members,</P><P>&nbsp;</P><P>I'm working on a requirement that demands grand difference (similar to grand total) in matrix view.</P><P>How to acheive difference column that is in red font below. I tried matrix view and I was able to get result except for difference column. Appreciate if there is a better way to acheive this. Thanks!</P><P>&nbsp;</P><P>Dataset: Table1</P><TABLE><TBODY><TR><TD>date</TD><TD>revenue</TD><TD>qty</TD><TD>orders</TD></TR><TR><TD>1/1/2018</TD><TD>100</TD><TD>1</TD><TD>1</TD></TR><TR><TD>1/2/2018</TD><TD>101</TD><TD>1</TD><TD>2</TD></TR><TR><TD>1/3/2018</TD><TD>102</TD><TD>1</TD><TD>3</TD></TR><TR><TD>1/4/2018</TD><TD>103</TD><TD>1</TD><TD>4</TD></TR><TR><TD>1/5/2018</TD><TD>104</TD><TD>1</TD><TD>5</TD></TR><TR><TD>1/6/2018</TD><TD>105</TD><TD>1</TD><TD>6</TD></TR><TR><TD>1/7/2018</TD><TD>106</TD><TD>1</TD><TD>7</TD></TR><TR><TD>1/8/2018</TD><TD>107</TD><TD>1</TD><TD>8</TD></TR><TR><TD>1/9/2018</TD><TD>108</TD><TD>1</TD><TD>9</TD></TR><TR><TD>1/10/2018</TD><TD>109</TD><TD>1</TD><TD>10</TD></TR><TR><TD>1/11/2018</TD><TD>110</TD><TD>1</TD><TD>11</TD></TR><TR><TD>1/12/2018</TD><TD>111</TD><TD>1</TD><TD>12</TD></TR><TR><TD>1/1/2019</TD><TD>112</TD><TD>1</TD><TD>13</TD></TR><TR><TD>1/2/2019</TD><TD>113</TD><TD>1</TD><TD>14</TD></TR><TR><TD>1/3/2019</TD><TD>114</TD><TD>1</TD><TD>15</TD></TR><TR><TD>1/4/2019</TD><TD>115</TD><TD>1</TD><TD>16</TD></TR><TR><TD>1/5/2019</TD><TD>116</TD><TD>1</TD><TD>17</TD></TR><TR><TD>1/6/2019</TD><TD>117</TD><TD>1</TD><TD>18</TD></TR><TR><TD>1/7/2019</TD><TD>118</TD><TD>1</TD><TD>19</TD></TR><TR><TD>1/8/2019</TD><TD>119</TD><TD>1</TD><TD><P>20</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Result:</P><P>&nbsp;</P><P>GPI,ROI,TT are all calculated measure.</P><P>ROI = Sum('Table1'[revenue])</P><P>TT = COUNT('Table1'[qty])</P><P>GPI = DIVIDE([ROI],[TT])</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>KPI</TD><TD>2018</TD><TD>2019</TD><TD><FONT color="#FF0000">DIFFERENCE</FONT></TD></TR><TR><TD>GPI</TD><TD>105.5</TD><TD>115.5</TD><TD><FONT color="#FF0000">10</FONT></TD></TR><TR><TD>ROI</TD><TD>1266</TD><TD>924</TD><TD><FONT color="#FF0000">342</FONT></TD></TR><TR><TD>TT</TD><TD>12</TD><TD>8</TD><TD><FONT color="#FF0000">4</FONT></TD></TR></TBODY></TABLE> Sat, 27 Apr 2024 22:12:58 GMT SaranyaDurairaj 2024-04-27T22:12:58Z How to calculate grand difference (similar to grand total) in matrix view https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-grand-difference-similar-to-grand-total-in/m-p/3872609#M151279 <P>Hi Members,</P><P>&nbsp;</P><P>I'm working on a requirement that demands grand difference (similar to grand total) in matrix view.</P><P>How to acheive difference column that is in red font below. I tried matrix view and I was able to get result except for difference column. Appreciate if there is a better way to acheive this. Thanks!</P><P>&nbsp;</P><P>Dataset: Table1</P><TABLE><TBODY><TR><TD>date</TD><TD>revenue</TD><TD>qty</TD><TD>orders</TD></TR><TR><TD>1/1/2018</TD><TD>100</TD><TD>1</TD><TD>1</TD></TR><TR><TD>1/2/2018</TD><TD>101</TD><TD>1</TD><TD>2</TD></TR><TR><TD>1/3/2018</TD><TD>102</TD><TD>1</TD><TD>3</TD></TR><TR><TD>1/4/2018</TD><TD>103</TD><TD>1</TD><TD>4</TD></TR><TR><TD>1/5/2018</TD><TD>104</TD><TD>1</TD><TD>5</TD></TR><TR><TD>1/6/2018</TD><TD>105</TD><TD>1</TD><TD>6</TD></TR><TR><TD>1/7/2018</TD><TD>106</TD><TD>1</TD><TD>7</TD></TR><TR><TD>1/8/2018</TD><TD>107</TD><TD>1</TD><TD>8</TD></TR><TR><TD>1/9/2018</TD><TD>108</TD><TD>1</TD><TD>9</TD></TR><TR><TD>1/10/2018</TD><TD>109</TD><TD>1</TD><TD>10</TD></TR><TR><TD>1/11/2018</TD><TD>110</TD><TD>1</TD><TD>11</TD></TR><TR><TD>1/12/2018</TD><TD>111</TD><TD>1</TD><TD>12</TD></TR><TR><TD>1/1/2019</TD><TD>112</TD><TD>1</TD><TD>13</TD></TR><TR><TD>1/2/2019</TD><TD>113</TD><TD>1</TD><TD>14</TD></TR><TR><TD>1/3/2019</TD><TD>114</TD><TD>1</TD><TD>15</TD></TR><TR><TD>1/4/2019</TD><TD>115</TD><TD>1</TD><TD>16</TD></TR><TR><TD>1/5/2019</TD><TD>116</TD><TD>1</TD><TD>17</TD></TR><TR><TD>1/6/2019</TD><TD>117</TD><TD>1</TD><TD>18</TD></TR><TR><TD>1/7/2019</TD><TD>118</TD><TD>1</TD><TD>19</TD></TR><TR><TD>1/8/2019</TD><TD>119</TD><TD>1</TD><TD><P>20</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Result:</P><P>&nbsp;</P><P>GPI,ROI,TT are all calculated measure.</P><P>ROI = Sum('Table1'[revenue])</P><P>TT = COUNT('Table1'[qty])</P><P>GPI = DIVIDE([ROI],[TT])</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>KPI</TD><TD>2018</TD><TD>2019</TD><TD><FONT color="#FF0000">DIFFERENCE</FONT></TD></TR><TR><TD>GPI</TD><TD>105.5</TD><TD>115.5</TD><TD><FONT color="#FF0000">10</FONT></TD></TR><TR><TD>ROI</TD><TD>1266</TD><TD>924</TD><TD><FONT color="#FF0000">342</FONT></TD></TR><TR><TD>TT</TD><TD>12</TD><TD>8</TD><TD><FONT color="#FF0000">4</FONT></TD></TR></TBODY></TABLE> Sat, 27 Apr 2024 22:12:58 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-grand-difference-similar-to-grand-total-in/m-p/3872609#M151279 SaranyaDurairaj 2024-04-27T22:12:58Z Re: How to calculate grand difference (similar to grand total) in matrix view https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-grand-difference-similar-to-grand-total-in/m-p/3873105#M151290 <P>Hi,</P> <P>I am not sure how your semantic model looks like, but one of ways is to create Calculation Groups.</P> <P>Please check the below picture and the attached pbix file.</P> <P>&nbsp;</P> <P><img /><img /></P> <P>&nbsp;</P> <P><img /></P> <P>&nbsp;</P> <P>&nbsp;</P> Sun, 28 Apr 2024 12:20:16 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-grand-difference-similar-to-grand-total-in/m-p/3873105#M151290 Jihwan_Kim 2024-04-28T12:20:16Z