Creating Matrix with Data from Unrelated Tables

[HabibAdil]

Hi,

Can someone please tell me how can I replicate this matrix in power bi (image 1). The Quote and Sales data are in different tables with different dates (quote received date, sales order date). I have extracted months and years from these dates.  However, I ended up with Month and Year Columns in each table. If I put month from one table in the Row option in the matrix, it does not work for another table as they do not have relationship. For example in below SO Header table is not connected/related to Quotes table. If I try to put the two dates (Month) columns from the two tables into the Row option , it creates hierarchy which I don't want.

I created my own calendar table (Month ad Year columns )and then connected these columns with Month and Year in the other tables. I used the calendar table to filter the year and I used the Month column in the Matrix Row option. It did not work.

Thanks, Habib

image 3

[v-jayw-msft]

Hi @HabibAdil ,

Refer the topic below see if it helps.

https://community.powerbi.com/t5/Desktop/Matrix-Combine-sales-per-month-columns-and-measures/m-p/951...

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HabibAdil]

Hi @v-jayw-msft 

Thanks for your help. Unfortunatley, this topic is not what I want. I might have to create a matrix for each initiative in excel sheet and put them on top of each other.

[v-jayw-msft · Accepted solution]

Hi @HabibAdil ,

Not sure if i get your point, but you can use SELECTEDVALUE() function if there's no relationship between two tables.

Example:

calculate(sum(column1),filter(table,table[date]=selectedvalue(calendar[date])))

https://docs.microsoft.com/en-us/dax/selectedvalue-function 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.