Creating a Matrix format in the Query Editor

[NZemel]

Hi,

Some Background:

I have to work on figuring out the movement of opportunities within our Salesforce along the sales stages setup in the system (negotiation, PO, Business Lost...). The thing is that some of these accounts have multiple opportunities running simaltaneously. I want a view in which I can see the stages and the total amount of money in each opportunity. The easy fix was creating a Matrix view in Salesforce and have tried to export this report to Power BI. The issue with that however is when I bring it into Power BI, it brings the whole list and takes it out of the Matrix setup I have in SF.

Below is what my data looks like in Power BI Query Editor:

 What I need this to look like in Power BI taken from an Excel PivotTable:

Questions:
1. Is there a way to bring it into Power BI in Matrix format?

1. Is there a way to build a Matrix in the Query Editor of Power BI?

2, Similar to Excel when you use PivotTables you can display "values" using "sum of amount", can this be done in Power BI?

[v-haibl-msft]

@NZemel

The solution provided by BhaveshPatel should be right, please try it and post back if you still have questions.

There is a Matrix visual in Power BI. We can shape and transform the data firstly in Query Editor and then drag the values into Matrix visual. In Query Editor, we can use Group By function to get sum of amount.

Best Regards,

Herbert

[NZemel]

@v-haibl-msft@BhaveshPatel

In response to the answers given.

I did go back into the query editor in order to begin setting up the table. If you look at the first picture I posted, it was not setup in the matrix form which Bhavesh showed in his pictures. In order to mimic his format, I created a conditional column for each stage which brought the amount of money in each stage per opportunity. Once I did this I used a group by in order to bring the data together.

[BhaveshPatel]

You can use pivot columns feature to achieve the desired matrix format result. However, there is already a inbuilt matrix visualization in powerbi (next to the table visual) that can be used as well.

For the transformation in Query Editor, Select the stage column and choose pivot column. As shown in the screenshot, Values section should be amount and aggrgation must be SUM to achieve expected output.

PIVOTOUTPUT

[NZemel]

If account name was the same lets say there is "A" and the amount is 1000 in Value Validation and another time "A" and the amount 4000 in Value Validation how do you connect both of them?

or in the case where an account has one opportunity at Value Validation and another at Business Lost, how do you connect them?

[BhaveshPatel · Accepted solution]

You can use "GROUPBY" operation before doing pivoting to achieve waht you are looking for.