Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rows transformation to columns

Hello Guys,   I have one column with mixed date and year. I need to convert into row format where each column representing a different column with different Month and Year.  Please see the attached...
PowerBI Server
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    It is suggested to create another table like below:

    Order Columns
    1 202001
    2 202002
    ...

    ...
    12

    202012
    13

    2020-Total
    14

    202101
    ...

    ...
    25

    202112
    26

    2021-Total
    ...

    ...

     

    And then, sort "Columns" column by "Order" column and put "Columns" column into Columns field of the Matrix visual. Then, create a measure like below and put it into Values field:

    Measure =
VAR Year_ =
    LEFT ( MAX ( 'Data'[DATE_ICD] ), 4 )
VAR Columns_ =
    MAX ( 'Table'[Columns] )
RETURN
    IF (
        Columns_ = "2020-Total",
        CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2020" ),
        IF (
            Columns_ = "2021-Total",
            CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2021" ),
            CALCULATE ( SUM ( 'Data'[Amount] ), 'Data'[DATE_ICD] = Columns_ )
        )
    )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks lbendlin  for your quick feedback. But my problem remains the same. May I was not clear about my problem before. What I want is that to show data in row format . For ex: 2020,2021,2

 

For a moment the data is in column format, but I need it to show like this where every Month, Year represents each column. I tried to do the same in Matrix view but there I am facing another problem. Whenever I am scrolling down, it not showing me the data.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

So basically what I am looking for is something like this. This is in matrix viz. But I think what my customer is looking for is to show in the way where after 2020 it will show the only total of 2020 month values and then for 2021, they want to add other columns like JAN 2021, Feb 2021, ..December 2021 and then the total of values. I don't know how it possible, do you have any idea?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    It is suggested to create another table like below:

    Order Columns
    1 202001
    2 202002
    ...

    ...
    12

    202012
    13

    2020-Total
    14

    202101
    ...

    ...
    25

    202112
    26

    2021-Total
    ...

    ...

     

    And then, sort "Columns" column by "Order" column and put "Columns" column into Columns field of the Matrix visual. Then, create a measure like below and put it into Values field:

    Measure =
VAR Year_ =
    LEFT ( MAX ( 'Data'[DATE_ICD] ), 4 )
VAR Columns_ =
    MAX ( 'Table'[Columns] )
RETURN
    IF (
        Columns_ = "2020-Total",
        CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2020" ),
        IF (
            Columns_ = "2021-Total",
            CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2021" ),
            CALCULATE ( SUM ( 'Data'[Amount] ), 'Data'[DATE_ICD] = Columns_ )
        )
    )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.