Create a 'virtual' matrix table based on 2 tables

[Anonymous]

I'm trying to combine two tables into a matrix. The use case is that I have sales in different categories and based on previous years I know the relative sales. Something like this:

	January
Candy	500
Vegetable	600
Juice	700

 and

February	March	April
10%	5%	1%

which I want to be able to create in something like this:

	January	February	March	April
Candy	500	550	525	505
Vegetable	600	660	630	606
Juice	700	770	735	707

I have tried different searches on the forum but nothing pops up that seems to solve this. Sorry if it is too obvious and thanks for any guidelines in this.

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, I did the test reference as follows:

Table =
VAR Feb =
    CALCULATE ( MAX ( Table2[Rate] ), Table2[Month] = "February" )
VAR Mar =
    CALCULATE ( MAX ( Table2[Rate] ), Table2[Month] = "March" )
VAR Apr =
    CALCULATE ( MAX ( Table2[Rate] ), Table2[Month] = "April" )
RETURN
    SUMMARIZE (
        Table1,
        Table1[Product],
        "Jan", MAX ( Table1[Value] ),
        "Feb",
            ( 1 + Feb )
                * MAX ( Table1[Value] ),
        "Mar",
            ( 1 + Mar )
                * MAX ( Table1[Value] ),
        "Apr",
            ( 1 + apr )
                * MAX ( Table1[Value] )
    )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[lbendlin]

Rearrange your data

Product	Value
Candy	500
Vegetable	600
Juice	700

Month	Rate
January	0%
February	10%
March	5%
April	1%

Then you can do a CROSSJOIN and add a calculated column  [Value]*(1+[Rate])