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
DeanBurston
New Member

Help with Matrix Visuals Please

Hello all,

 

I am reaching out to this community for some assistance please if someone could kindly help. 

 

I am in the process of converting a legacy report from Excel into Power BI.  The report uses pivots, the totals are then used to create a final £££ calculated output.  In Power BI, the matrix visuals have been utilised and I am happy with these; where I have a question, what is the best approach to use the totals from Matrix A (minus) the totals from Matrix B, to replicate my final £££ total please? 

 

This is super simple in Excel to handle; how is this best handled in Power BI please?

 

If someone could kindly feed in here, please I would be grateful?

3 REPLIES 3
DataNinja777
Skilled Sharer
Skilled Sharer

Hi @DeanBurston ,

 

Power BI and power pivot dax engine handles (consumes) the data in structured table format well, therefore, before bringing in the data in Power BI data model, it is better to unpivot the matrix format data through power query, and what's on row headers and column header in your matrix tables in Excel should be separated out in dimension tables in Power BI data model, and then relationships are created between your dimension tables and your two fact tables. You then aggregate the values' fields of your fact tables in measures, and subtract one measure from the other measure to get your required output.  

Idrissshatila
Super User
Super User

Hello @DeanBurston ,

 

you can't just use a matrix total like that, but you can create a measure that sums a column with different filters.

 

use something like this, but with your data https://learn.microsoft.com/en-us/dax/calculate-function-dax

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




nirali_arora
Resolver II
Resolver II

You will have to create measures for that. In case you need grand total, the following videos can help

Percent of Total and Percent of SubTotal   

https://www.youtube.com/watch?v=6jTildcV2ho
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s

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.

Top Solution Authors