Matrix with additional columns

[vipett]

I use a matrix to to display Forecast per month by product. As I also want to compare the average first six months' forecast with same period last year and what we have on orders. Today I do that in a separate matrix overlapped by the forecast by month table. 

That works fine as long as I don't need to scroll vertically, then you would scroll both and try to line them up.

Is there a way of adding three "additional" columns with a measure, or do I need to rebuild the data tables to have those three "as if they months"?

[danextian · Accepted solution]

Hi @vipett ,

Achieving your goal is possible, but it involves utilizing advanced DAX/PQ techniques. Essentially, you need to generate a table with a column storing the period, total, and additional columns. This task can be accomplished using either DAX or M. Subsequently, you create a measure to retrieve the desired value based on the specified period/total/extra columns. The structure of your table would resemble the following:

Please see attached sample pbix.

[vipett]

Thanks, did it virtually this way and that worked. 

Created a new crossjoin with a new table with these additional ones and the summarized it all