Showing data like a Matrix using T-SQL

[arturopaz]

Hello everyone, 

I am new to Power BI, I hope you can help me with this question.

I have a data model like the following one (where I have two fact tables and one dimension table,  )

Data model

Using a matrix I have a report like above.  How could I perform the equivalent of that  using T-SQL?

I've tried to do it but only managed to get one side

Thanks in advance

[amitchandak]

@arturopaz , what is the issue you are facing. A left join is with +0 or using Option, Show Item with no data.

Can you explain the issue you are facing. Are qty wrong?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data?

[arturopaz]

@amitchandak  what I am looking for is to show the data as shown in the Matrix but using T-SQL within MSSQL. (I share sample data)  Thanks in advance.

AW_Returns  (Fact_table)

AW_Sales (Fact_table)

AW_Territories_Lookup (Dimension table)

[sevenhills · Accepted solution]

I see, the tables are at different grain.

Try this SQL

SELECT
IsNull(a1.SalesTerritoryKey, a2.SalesTerritoryKey) as SalesTerritoryKey
, a1.ReturnQuantity
, a2.OrderQuantity
FROM (
SELECT tlk.SalesTerritoryKey
, SUM(s.OrderQuantiry) OrderQuantity
FROM AW_Territories_Lookup tlk
INNER JOIN AW_Sales s on tlk.SalesTerritoryKey = s.TerritoryKey
group by tlk.SalesTerritoryKey
) a2
LEFT JOIN (
SELECT tlk.SalesTerritoryKey
, SUM(r.ReturnQuantity) ReturnQuantity
FROM AW_Territories_Lookup tlk
INNER JOIN AW_Returns r on tlk.SalesTerritoryKey = r.TerritoryKey
GROUP BY tlk.SalesTerritoryKey
) a1 on a1.SalesTerritoryKey = a2.SalesTerritoryKey
Order by SalesTerritoryKey

[arturopaz]

@sevenhillsThanks you very much, that is what I was looking for, I wanted to replicate what a Matrix did internally in Power BI (or Pivot Table on Excel).

[amitchandak]

@arturopaz , One Option is to add +0 to both you measures

The second Option to use Show items with No Data

[sevenhills]

SELECT tlk.SalesTerritoryKey
, SUM(r.ReturnQuantity) ReturnQuantity
, SUM(s.OrderQuantiry) OrderQuantity
FROM AW_Territories_Lookup tlk
LEFT JOIN AW_Returns r on tlk.SalesTerritoryKey = r.TerritoryKey
LEFT JOIN AW_Sales s on tlk.SalesTerritoryKey = s.TerritoryKey
GROUP BY tlk.SalesTerritoryKey
Order by tlk.SalesTerritoryKey

With rollup -- if you want totals

[arturopaz]

@sevenhills Thanks for your answer, but running the query I get this:

I would like it to show like this:

[camargos88]

@arturopaz ,

If you remove the Order Quantity column, you only get the matching rows. To show also the nonmatching you need to select the option "Show Items with no data" clicking on the arrow of SalesTerritoryKey and checking the option "Show Items with no data".