Forum Discussion
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
9 Replies
Anonymous 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 🙂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.
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