Forum Discussion

greenskmachine's avatar
greenskmachine
Frequent Visitor
1 year ago

Matrix row subtotal issue

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 o...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
greenskmachine
Frequent Visitor
1 year ago

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.