Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ak77
Helper V
Helper V

SubTotal Adjustment for Matrix

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

ak77_1-1698695905559.png

 

 

1 ACCEPTED SOLUTION

To do this you need to create a Fund Cert2 column

Screenshot_1.png

try

View solution in original post

19 REPLIES 19
Ahmedx
Super User
Super User

please try full solution

 

@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

 

ak77_0-1698764671348.png

 

To do this you need to create a Fund Cert2 column

Screenshot_1.png

try

@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

ak77_0-1700027636851.png

 

pls try

 

@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 

ak77_0-1700088867854.png

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

ak77_2-1700089124876.png

 

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

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?

 

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

 

pls try

 

@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 !

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

Thanks @Ahmedx . wil check and get back to u

Ahmedx
Super User
Super User

pls try
I did it only for C3

 

Ahmedx
Super User
Super User

do you need it like this?

Screenshot_1.png

or this

Screenshot_2.png

@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?

 

ak77_2-1698726104588.png

 

 

 

 

 

 

did I understand you correctly?

Screenshot_4.png

ak77
Helper V
Helper V

@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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors