Matrix row subtotal issue

[greenskmachine]

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 

[v-prasare]

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

[v-prasare]

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

[v-prasare]

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

[Anonymous]

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.

[greenskmachine]

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. 

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[greenskmachine]

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

[lbendlin]

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])