How to show Columns one after another in matrix

[mruhullah]

Need help to place Revenue, Forecast and Plan one after another in Matrix. 

I wanted to see the below result. 

your help will be much appreciated

[mruhullah]

Thanks but my model is already complex and table got derived from 7 different tabnle based on DAX. this is the result table. 

Idea is great and i need to think before i implement this as it is going to make my model more complex.

[PaulDBrown · Accepted solution]

There's is no way around creating a new table for the matrix setup. You can however avoid the period table if you so wish, and just use TREATAS in your measures:

 and this measure:

MEASURE formatrix =
    VAR _Revenue =
        CALCULATE (
            SUM ( fTable[Revenue] ),
            TREATAS ( VALUES ( 'Matrix Columns Setup'[FY-Q] ), 'Date Table'[FY-Q] )
        )
    VAR _Forecast =
        CALCULATE (
            SUM ( fTable[Forecast] ),
            TREATAS ( VALUES ( 'Matrix Columns Setup'[FY-Q] ), 'Date Table'[FY-Q] )
        )
    VAR _Plan =
        CALCULATE (
            SUM ( fTable[Plan] ),
            TREATAS ( VALUES ( 'Matrix Columns Setup'[FY-Q] ), 'Date Table'[FY-Q] )
        )
    RETURN
        SWITCH (
            SELECTEDVALUE ( 'Matrix Columns Setup'[Order] ),
            1, _Revenue,
            2, _Forecast,
            3, _Plan
        )

New file attached

[mruhullah]

Thanks @PaulDBrown let me give it a try....

[PaulDBrown]

Here is one way of setting it up. 

Assuming you have a fact table along these lines

 And you have dimension tables for date and product, so the model looks like this

You need to create two new tables: one for the matrix columns and a bridge table for the periods:

Matrix Columns Setup = 
VAR _Metric = {("Revenue", 1), ("Forecast", 2), ("Plan", 3)}
VAR _Period = VALUES('Date Table'[FY-Q])
RETURN
CROSSJOIN(_Metric, _Period)

 and the period table

Now create this measure for the matrix (based on SUM measures for each metric):

Measure for matrix = 
SWITCH(
    SELECTEDVALUE('Matrix Columns Setup'[Order]),
    1, [Sum Revenue],
    2, [Sum Forecast],
    3, [Sum Plan])

Finally, set up the matrix with the Porduct Table[Product] as rows, the 'Matrix column Setup'[Metric] and the Period[FY-Q] as the columns and the [Measure for matrix] in the values bucket:

 Sample PBIX file attached

[mruhullah]

Thanks but my model is already complex and table got derived from 7 different tabnle based on DAX. this is the result table. 

Idea is great and i need to think before i implement this as it is going to make my model more complex.

[v-yadongf-msft]

Hi @mruhullah,

Is this the result you want?

My test table:

Remember to expand all done one level in the hierarchy:

Best regards,

Yadong Fang

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