SubTotal Adjustment for Matrix

[ak77]

Hi Experts,

Please check and help on this if possible.

I have the below Table display  and have the requirement as follows,

1. Do not change anything on Main Total 

2.  Total of A , B , C from Classification c1 should be the value of c1 ie 16487 and not the default sum A+B+C 

H, i, j, K should be 15860 and not 15960. 

3. Hide c1,c2, c3 

Can you please help on this. PBIX is shared in the below link

https://drive.google.com/file/d/1y5NElxUfhnx4awmd2wXy3dlb8RRrNCxJ/view?usp=sharing

[Ahmedx]

please try full solution

[ak77]

@Ahmedx , Thanks a lot for this.  The values look good . but it works only for c1, c2 ,c3 while this can be extended to many classification liks c4,e1, etc

Can u please help making it generic? comparing Classification  and FundCert values c1=c1, then replace the total value?  is this possible? please let me know

[Ahmedx · Accepted solution]

To do this you need to create a Fund Cert2 column

try

[ak77]

@Ahmedx , Need a help on the solution provided ... MAIN TOTAL should exclude classification Total(38065) .so  MAIN TOTALshould be 16487+5718+15860=38065

Please check

[Ahmedx]

pls try

[ak77]

@Ahmedx , Thanks for reply. i tried with actual dataset and it worked fine.. thanks again... but unfortunately one more issue has come.. The user has introduced a column record type on which total has to be taken.. for ex: The below 2 numbers of record type 'Total' should be added and displayed in main Total for selected effective date range

141603149.23+-191498528.13=-49895378.90 

Below is the report snapshot.The Main Total which is blank should have:-49895378.90 

The actual report is avaialble in below link.. Please please help ! let me know a new ticket needs to be thread for this

https://drive.google.com/file/d/14wMrb3AIg8y-zcmEEbAh7SKV3qX7AwT5/view?usp=sharing

[Ahmedx]

why do you need to minus these two amounts? (141603149.23+-191498528.13=-49895378.90)

They don't have an ID so it's difficult.
you can add index column in power query?

[ak77]

@Ahmedx , Sorry. thats the requirement from users.. and its the sum of those 2 values not minus..

[Ahmedx]

pls try

[ak77]

@Ahmedx , Thanks a ton for ur help on this solution. it has been implemented on actual dataset and sent for users testing. accepting as solution ! double likes !

[ak77]

Thanks again @Ahmedx , i will implement it in my original dataset and get back to u in a while

[ak77]

Thanks @Ahmedx . wil check and get back to u

[Ahmedx]

pls try
I did it only for C3

[Ahmedx]

do you need it like this?

[Ahmedx]

or this

[ak77]

@Ahmedx , Thanks for reply.

i need the full values of A, B C etc to be displayed as below . Only Total value of A b c should be replaced by value in C1, D E G F Total should be replaced by  C2 , H, I , j ,k should be replaced by C3 values 

is this possible?

[Ahmedx]

did I understand you correctly?

[ak77]

@Greg_Deckler , hi greg, Thanks for reply. Can u please help how ur sample DAX can be used for my scenario . i tried and was not able to proceed.

Thanks again

[Greg_Deckler]

@ak77 Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community