cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DivyaNair2022
Frequent Visitor

Matrix total calculations

Hi,

 

I am very new to powerBi and i am trying to put up a matrix as shown below but stuck with the difference calculation.

I am not sure how to show the total which is the sum of (cat1+cat2+cat3_cat4+cat5 - Withdrawn)

DivyaNair2022_0-1662940365638.png

Any help would be appreciated

Thanks

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You have shared how you want the result to look not how your source data is arranged.  Share that in a form that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

IDNotice TypeIssued Date
1Category 121/04/2022
2Category 116/05/2022
3Category 213/08/2022
4Category 312/07/2022
5Category 41/01/2022
6Category 513/09/2022
7Withdrawn13/01/2022

 

This is how my Data source looks like.    
Report should have the total by the Notice type issued in a quarter.  
If there is a withdrawn the total should minus that from the sum of the other categories

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Many Thanks @Ashish_Mathur it works the way i wanted. 

You are welcome.  Please mark my previous reply as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DivyaNair2022
Frequent Visitor

Many thanks @preranaharish . I will give it a try and let you know.

Please mark it as a solution if it helped you achieve the result

 

preranaharish
New Member

@DivyaNair2022 Do a conditional replace using power query and multiply the row where the NoticeType is "Withdrawn" by -1, something like:  Table.ReplaceValue(#"Renamed Columns",each [Q1 Jan Mar],each if [Notice Type]="Withdrawn" then [Q1 Jan Mar]*-1 else [Q1 Jan Mar],Replacer.ReplaceValue,{"Q1 Jan Mar"}).

Then,

Create a custom Column as 

Total = 'Table'[Q1 Jan Mar]+'Table'[Q2 Apr Jun]+'Table'[Q3 Jul Sep]
 
You will get your desired Total.
 
Hope this helps 🙂
 
 

sorry i am a beginner and i couldn't work it out 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.