Forum Discussion
Need help in dax while using matrix visual - Row total is not giving me the correct result
Can anyone of you help me to figure out why my subtotals are not populating here please? If i add the all selected to the product level still it end up giving me the wrong result in matrix visual. Please let me know if you need any more information. Thanks!
In matrix I have product level on top and the site names are at the sublevel in matrix rows.
Anonymous Thanks for your reply. Still, your measure is missing overall total row value. Here is the correct measure which helps me to acheieve desired result
Proj. Annual Var =VAR PYI1 = [Predicted Year Income 1]VAR PYI2 = [Predicted Year Income 2]VAR Result =IF(ISINSCOPE(Sites[Name]) && ISINSCOPE(BITransactions[ProductName]),PYI1 - PYI2,SUMX(SUMMARIZE(BITransactions,Sites[Name],BITransactions[ProductName],"Diff", [Predicted Year Income 1] - [Predicted Year Income 2]),[Diff]))RETURN Result
11 Replies
Venkatesh_ In your screenshot, last line (highlighted in red) is a SUBTOTAL VALUE or a site name VALUE ? I didn't see subtotal in your image attached, share a sample dataset or clear picture. Thank you
fahadqadir3 First row is the sub total(row) of the product, attaching the full matrix visual, the grand total also shows zero for the meaures above, If you see the first line, It should result approx "90.20" = (-185+90.48), same follows for variance of avg sell price as well.
Hi Venkatesh_ ,
Thanks for the reply from fahadqadir3 , please allow me to provide another insight:
It seems that it may be a problem with the measure total. You can use the ISINSCOPE function to control different levels to show different results.
Measure = IF(ISINSCOPE(financials[Product]),"aaa",IF(ISINSCOPE('financials'[Country]),"bbb"))In addition, these links may be helpful to you:
Dealing with Measure Totals - Microsoft Fabric Community
Measure Totals, The Final Word - Microsoft Fabric Community
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous It seems like I cannot use the INSCOPE function in my measure, attaching the snap for your reference
Hi Venkatesh_ ,
This function is ISINSCOPE instead of INSCOPE. Also, please check whether the difference between your two numbers in the parent level is 0. If it is 0, you need to rewrite the calculation for it and then use the ISINSCOPE function to return its value.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Venkatesh_ ,
You can try this.
Measure 4 = var _table1= DISTINCT('BITransactions'[ProductName]) var _table2= DISTINCT('Sites'[Name]) var _table3= CROSSJOIN( _table1,_table2) var _table4= ADDCOLUMNS( _table3,"1",[Predicted Year Income 1], "2",[Predicted Year Income 2]) var _table5= ADDCOLUMNS( _table4,"3",[1] - [2]) var _value= SUMX( FILTER( _table5,[ProductName]=MAX('BITransactions'[ProductName])),[3]) return IF( HASONEVALUE( 'Sites'[Name]),[Proj. Annual Var], IF( HASONEVALUE(BITransactions[ProductName]),_value))Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Thanks for your reply. Still, your measure is missing overall total row value. Here is the correct measure which helps me to acheieve desired result
Proj. Annual Var =VAR PYI1 = [Predicted Year Income 1]VAR PYI2 = [Predicted Year Income 2]VAR Result =IF(ISINSCOPE(Sites[Name]) && ISINSCOPE(BITransactions[ProductName]),PYI1 - PYI2,SUMX(SUMMARIZE(BITransactions,Sites[Name],BITransactions[ProductName],"Diff", [Predicted Year Income 1] - [Predicted Year Income 2]),[Diff]))RETURN Result
