Merging two Matrix tables into a single Matrix table

[hcknbrgr]

Hi all, fairly new to Power BI and DAX.

I have created two Matrix tables. Matrix Table 1 uses Product in the Columns section to split out the counts of product purchases.  Table 2 has no Columns field and just has the sum of purchases.  Both tables have a filter to only include records with a status of New.  I would like to create a single table to display that has the results of both tables. 

Here is my data and the table I want to create

Data Table

If I include the Amount column in my table1 values, I get the following, which is not the desired result.

I could create Measures for Product A, Product B, etc., to create the table, but then whenever a new product is created a new measure would need to be created.  It seems like there ought to be a way to create this more dynamically, hopefully soemone can point the way.

Thank you,

Greg

[hcknbrgr]

In the end I went with creating a new calculated column for each product and built the table with fixed columns.  When I created the Dax table my data was now split into two tables and filters on other fields not included in the sample were not applied to the DAX table, so it was easier to keep everything in one table.

[Massig]

Hi 

I am trying to do the same ... 

I am not sure how you did manage to solve this...

I wonder if you could share a bit more details on how you did it...

I have 2 tables ... (data is coming from 2 data sets linked)

One where I have

CUSTOMER NAME / FIRST ACTIVITY / ACTIVITY TYPE / LAST ACTIVITY / ACTIVITY TYPE

and a second matrix visualization

CUSTOMER NAME / ACTIVITY TYPE 1 / ACTIVITY TYPE 2 etc ... and count of activity type as value.

I was trying to have a combined visualization that shows

CUSTOMER NAME FIRST ACTIVITY / ACTIVITY TYPE / LAST ACTIVITY / ACTIVITY TYPE and then the matrix part with the count of activity by type.

[hcknbrgr]

I was hoping that I could create a DAX statement that would store the values of Table 1 and then I could pull that table and the Sum of Amount from my main data table.  But I am not there yet in my understanding of DAX.

I was able to find a DAX statement that I could modify and get the desired result by hard coding each Product, but again, this needs to be updated whenever there is a new Product introduced:

[v-yulgu-msft]

Hi @hcknbrgr,

You could submit this requirement as an idea at this page:How can we improve Power BI?  If it mentioned by many users, our product team might consider adding this feature in later release.

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @hcknbrgr,

It is not available to achieve that. By default, we add a column/measure into value section in a matrix visual, it will be grouped automatically. As it shows in above image, the measure/column will show for each group instance.

Best regards,
Yuliana Gu

[sdjensen]

Imagine how awesome it would be if you were able to hide all values of a measure in a table/matrix for still show the total.

I believe this feature is coming to the new table/matrix visuals, but is't not there yet.