Matrix row subtotal issue

Question

Hi team,&nbsp;I have the below matrix that has measures for Sum Total Amount, Latest Transaction Date, and Latest Transaction Date&nbsp; Total Amount.&nbsp;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?&nbsp;The overall total should also be 1500.&nbsp;&nbsp;Thanks and kind regards.&nbsp;sample.pbix&nbsp;

lbendlin · Answer

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

greenskmachine · Answer

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

anonymous · Answer

Hi,&nbsp;greenskmachine&nbsp;
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 &amp; 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)
&nbsp;
&nbsp;
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

greenskmachine · Answer

Thanks for the reply.&nbsp;I have tried this in my actual file, and it has changed the subtotal value.&nbsp;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.&nbsp;&nbsp;Apologies I can't share my actual file.&nbsp;

lbendlin · Answer

&nbsp;
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])
&nbsp;

Forum Discussion

Matrix row subtotal issue

8 Replies

Related content

Customize subtotal in matrix

Matrix subtotal

Matrix Subtotal Issue

Subtotals in Matrix

Hide duplicative subtotals in matrix