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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User
Super User

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors