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
Fabcon_Europe_Social_Bogo

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

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.