Help with Matrix Visuals Please

[DeanBurston]

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?

[DataNinja777]

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]

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 💡

[nirali_arora]

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