Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.