Forum Discussion

JrobertsUK's avatar
JrobertsUK
New Member
8 years ago
Solved

Trying to recreate pivot table in Power Bi- Help

Hi All,


New to Power BI.

Loving it, but having an issue.


I am trying to recreate the below pivot table

The Total Aged Stock column is currently a sum in excel, adding various aging "buckets"

 

I am able to recreate the majority of the table in Power BI, with the exception of this one column.


I believe this may be achiveable via measures or a calculated column, but am struggling to find a working solution.


Any help is appriciated, thanks all.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi JrobertsUK,

     

    Based on my assumption, I created a sample data table named as 'Test2'.

     

    In above table, please create two calculated columns:

    sum over 60 days =
CALCULATE (
    SUM ( 'Test 2'[Ages] ),
    FILTER (
        ALLEXCEPT ( 'Test 2', 'Test 2'[Customer NO] ),
        'Test 2'[Cat] <> "0-30 days"
            && 'Test 2'[Cat] <> "31-60 days"
    )
)

Sum total =
CALCULATE (
    SUM ( 'Test 2'[Ages] ),
    ALLEXCEPT ( 'Test 2', 'Test 2'[Customer NO] )
)

     

    Then, create a calculated table like below:

    summarize table =
UNION (
    SELECTCOLUMNS (
        'Test 2',
        "Customer NO", 'Test 2'[Customer NO],
        "Age", 'Test 2'[Ages],
        "Cat", 'Test 2'[Cat]
    ),
    SUMMARIZE (
        'Test 2',
        'Test 2'[Customer NO],
        "Age", AVERAGE ( 'Test 2'[sum over 60 days] ),
        "Cat", "Over 60 days"
    ),
    SUMMARIZE (
        'Test 2',
        'Test 2'[Customer NO],
        "Age", AVERAGE ( 'Test 2'[Sum total] ),
        "Cat", "Total"
    )
)

    Add corresponding fields from above new table into matrix visual. Remember to turn off the "Column Total" under format panel.

     

    To follow RolandsP's suggestion that use Drill Down as a workaround, please see below steps.

    In report view, choose the [Cat] column, meanwhile, click the "New Group" button.

     

    Set groups like below:

     

    After than, you will get a new group column in original table. Add the new group to "Columns" section in Matrix visual.

     

    On top level, matrix show total values for "Over 60 days". When you click the highlighted drill down icon, matrix will show total values across all groups.

     

    Best regards,

    Yuliana Gu

4 Replies

  • RolandsP's avatar
    RolandsP
    Icon for Resolver IV rankResolver IV
    8 years ago

    You need to run new grouping ( using New Groups) on your ageing column and classify it in 3 groups: 0-30 Days, 31-60 Days and Over 60 Days (this would include all your groups over 60 days). And then in the matrix, you would include two levels in columns - New Grouping column, and existing ageing column. Then you will be able to drill in first level group Over 60 days to show more granular breakdown. This would be a little bit different structure from Excel pivotable, but very close one.

    • JrobertsUK's avatar
      JrobertsUK
      New Member
      8 years ago

      Thanks for the rapid response Roland. I've tried to do this as per your instructions, but am struggling. Do you have any practical examples?

      • RolandsP's avatar
        RolandsP
        Icon for Resolver IV rankResolver IV
        8 years ago

        You need to do following steps:

        1) Select the current grouping column 

        2) Then you need to select New Group under Modelling tab.

        3) There from Ungrouped value you select current group 0-30 Days and press Group, then select 31-60 days and press Group, then select all remaining values holding CTRL and press Group. You can change the name for this new group. 

        4) Now you have new column in the table that you can include in columns in matrix.

         

         

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi JrobertsUK,

     

    Based on my assumption, I created a sample data table named as 'Test2'.

     

    In above table, please create two calculated columns:

    sum over 60 days =
CALCULATE (
    SUM ( 'Test 2'[Ages] ),
    FILTER (
        ALLEXCEPT ( 'Test 2', 'Test 2'[Customer NO] ),
        'Test 2'[Cat] <> "0-30 days"
            && 'Test 2'[Cat] <> "31-60 days"
    )
)

Sum total =
CALCULATE (
    SUM ( 'Test 2'[Ages] ),
    ALLEXCEPT ( 'Test 2', 'Test 2'[Customer NO] )
)

     

    Then, create a calculated table like below:

    summarize table =
UNION (
    SELECTCOLUMNS (
        'Test 2',
        "Customer NO", 'Test 2'[Customer NO],
        "Age", 'Test 2'[Ages],
        "Cat", 'Test 2'[Cat]
    ),
    SUMMARIZE (
        'Test 2',
        'Test 2'[Customer NO],
        "Age", AVERAGE ( 'Test 2'[sum over 60 days] ),
        "Cat", "Over 60 days"
    ),
    SUMMARIZE (
        'Test 2',
        'Test 2'[Customer NO],
        "Age", AVERAGE ( 'Test 2'[Sum total] ),
        "Cat", "Total"
    )
)

    Add corresponding fields from above new table into matrix visual. Remember to turn off the "Column Total" under format panel.

     

    To follow RolandsP's suggestion that use Drill Down as a workaround, please see below steps.

    In report view, choose the [Cat] column, meanwhile, click the "New Group" button.

     

    Set groups like below:

     

    After than, you will get a new group column in original table. Add the new group to "Columns" section in Matrix visual.

     

    On top level, matrix show total values for "Over 60 days". When you click the highlighted drill down icon, matrix will show total values across all groups.

     

    Best regards,

    Yuliana Gu