cancel
Showing results for
Did you mean:
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)

Any help would be appreciated

Thanks

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
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
Frequent Visitor
 ID Notice Type Issued Date 1 Category 1 21/04/2022 2 Category 1 16/05/2022 3 Category 2 13/08/2022 4 Category 3 12/07/2022 5 Category 4 1/01/2022 6 Category 5 13/09/2022 7 Withdrawn 13/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
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Many Thanks @Ashish_Mathur it works the way i wanted.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

New Member

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

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 🙂

Frequent Visitor

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

Announcements

#### 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.

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

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors