Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |