Values on rows in combination with measures from different data sets

[Anonymous]

Hi,

I need to create the below table. I have different data sets for Product A, B and C, so I basically have a measure for Product A Qty sold, Product B Qty sold, Product C Qty Sold, Product A Qty sold same period last year, Product B Qty sold same period last year and so on. If I use "Show on rows" I get all the measures on the rows, which is not what I want, so I would like to ask if there is a way to acheive the below without merging the product data into the same dataset?

[v-xicai · Accepted solution]

Hi @Anonymous ,

If you have three tables, which are Product A , Product B and Product C, then you may create a calculated table like DAX below to get all product, create relationship with the other three Product tables with Both of Cross filter direction.

Intermediate Table =
UNION (
    DISTINCT ( 'Product A'[Product] ),
    DISTINCT ( 'Product B'[Product] ),
    DISTINCT ( 'Product C'[Product] )
)

Then you may put the 'Intermediate Table'[Product] into the Rows box of Matrix visual, put other created measures into Values box.

Best Regards,

Amy 

Community Support Team _ Amy

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

[Greg_Deckler]

@Anonymous - Checking, did this fix your issue?

[Greg_Deckler]

@Anonymous - Don't use show on rows, just put Product in your Rows and your measures in your Values:

[amitchandak]

@Anonymous , I doubt if wnt to measure to merge under the category you can get it without merge and segmentation

column header grouping
https://www.daxpatterns.com/dynamic-segmentation/
https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905
https://community.powerbi.com/t5/Desktop/grouping-measure-results/td-p/456795