topic Re: Matrix row subtotal issue in DAX Commands and Tips

Matrix row subtotal issue

greenskmachine — Tue, 11 Mar 2025 02:56:01 GMT

Hi team,

I have the below matrix that has measures for Sum Total Amount, Latest Transaction Date, and Latest Transaction Date Total Amount.

In the maxtrix subtotal "C1", it returns a value of 500. Instead of showing a subtotal of 500, how can I sum the Latest Transaction Totals together, so the C1 subtotal row shows 1500 (the sum of what's beneath it), instead of 500?

The overall total should also be 1500.

Thanks and kind regards.

sample.pbix

Re: Matrix row subtotal issue

lbendlin — Tue, 11 Mar 2025 00:25:20 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Matrix row subtotal issue

greenskmachine — Tue, 11 Mar 2025 02:57:52 GMT

I have updated my post and added a simple pbix example, similar to my actual file.

Re: Matrix row subtotal issue

Anonymous — Tue, 11 Mar 2025 03:16:04 GMT

Hi, greenskmachine

It is normal for you to encounter the current problem, for subtotal rows, it returns the maximum date of type C1. Then calculate the total sales of all orders of type =C1 & Date=27/02/2025 in the calculate function.

All other sales data that do not meet the above conditions of date=27/02/2025 are filtered out.

You can use iSinscope to solve the problem. To do this, I've created a simple sample:

Measure = VAR _total = SUM('Table'[Column4]) RETURN IF(ISINSCOPE('Table'[Column2]),CALCULATE(SUM('Table'[Column4]),FILTER('Table','Table'[Column3]=MAX('Table'[Column3]))),_total)

Best Regards

Jianpeng Li

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

Re: Matrix row subtotal issue

greenskmachine — Tue, 11 Mar 2025 03:46:54 GMT

Thanks for the reply.

I have tried this in my actual file, and it has changed the subtotal value.

However it is now returning the unfiltered total for the month, rather than the sum of the latest filtered data. See below. This subtotal value should be a lot lower.

Apologies I can't share my actual file.

Re: Matrix row subtotal issue

lbendlin — Tue, 11 Mar 2025 22:19:38 GMT

Latest Transaction Date Total Amount = var a = ADDCOLUMNS(summarize(Sheet1,[Supplier]),"ltd",CALCULATE(max(Sheet1[TransactionDate]))) var b = ADDCOLUMNS(a,"ltv",var s=[Supplier] var d=[ltd] return CALCULATE(sum(Sheet1[Amount]),Sheet1[Supplier]=s,Sheet1[TransactionDate]=d)) return sumx(b,[ltv])

Re: Matrix row subtotal issue

v-prasare — Fri, 09 May 2025 05:07:31 GMT

Hi greenskmachine,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Matrix row subtotal issue

v-prasare — Wed, 14 May 2025 04:47:06 GMT

Hi @greenskmachine,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Matrix row subtotal issue

v-prasare — Tue, 20 May 2025 13:48:27 GMT

Hi @greenskmachine,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query