cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

Predicted Year Income 1 =
CALCULATE(
COUNT(BITransactions[Shopping List Tranasaction_FK]),
DISTINCT(BITransactions[Shopping List Tranasaction_FK]),
DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -7, DAY)
) * 52 * ROUND(
CALCULATE(
AVERAGE('BITransactions'[Price])
),
2
)

Predicted Year Income 2 =   CALCULATE(
COUNT(BITransactions[Shopping List Tranasaction_FK]),
DISTINCT(BITransactions[Shopping List Tranasaction_FK]),
DATESINPERIOD(
Calendar[Date],
MAX(Calendar[Date]),
-7,
DAY
)
) * 52 * ROUND(
CALCULATE(
AVERAGE('BITransactions'[Price]),
ALLSELECTED(Sites[Name])
),
2
)

Proj. Annual Var =

VAR PYI1 = [Predicted Year Income 1]

VAR PYI2 = [Predicted Year Income 2]

VAR Result = PYI1 - PYI2

RETURN Result

Variance of Avg Sell Price =

VAR AvgPrice = ROUND(AVERAGEX(BITransactions,'BITransactions'[Price]), 2)

VAR OtherAvg = ROUND(CALCULATE(
AVERAGEX(BITransactions,'BITransactions'[Price]),
ALLSELECTED('Sites'[Name])), 2)

VAR Result = AvgPrice - OtherAvg

RETURN
IF(AvgPrice = BLANK(),BLANK(),Result)

1 ACCEPTED SOLUTION
Frequent Visitor

@v-kaiyue-msft   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 11
Community Support

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=
_table3,"1",[Predicted Year Income 1],
"2",[Predicted Year Income 2])
var _table5=
_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.

Frequent Visitor

@v-kaiyue-msft   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

Community Support

Hi @Venkatesh_ ,

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"))``````

Dealing with Measure Totals - Microsoft Fabric Community

Measure Totals, The Final Word - Microsoft Fabric Community

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.

Frequent Visitor

@v-kaiyue-msft  It seems like I cannot use the INSCOPE function in my measure, attaching the snap for your reference

Community Support

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.

Frequent Visitor

Can you send me the revised measure please? I can't achieve yet.

Community Support

Hi @Venkatesh_ ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

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.

Frequent Visitor

Hi @v-kaiyue-msft  Attaching the PBIX sample file link for your reference. It says the problem with the matrix visual on Proj annual variance and variance on sell price. You wont see any sub total or total rows and if you see they are adding incorrect results. Thanks.

Frequent Visitor

@v-kaiyue-msft  any update on this problem please?

Super User

@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

Frequent Visitor

@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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors