Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi @jeroenb42 ,
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.
Hi @jeroenb42 ,
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.
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])
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |