Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a 'virtual' matrix table based on 2 tables

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
Candy500
Vegetable600
Juice700

 and

FebruaryMarchApril
10%5%1%

 

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

 JanuaryFebruaryMarchApril
Candy500550525505
Vegetable600660630606
Juice700770735

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.

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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])

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.