Matrix total calculations

[DivyaNair2022]

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

[Ashish_Mathur]

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.

[DivyaNair2022]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[DivyaNair2022]

Many Thanks @Ashish_Mathur it works the way i wanted. 

[Ashish_Mathur]

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

[DivyaNair2022]

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

[preranaharish]

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

[preranaharish]

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

[DivyaNair2022]

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