Forum Discussion

Mr_Stern's avatar
Mr_Stern
Frequent Visitor
2 years ago
Solved

calculating diferences between columns in matrix

Hi,

Still a beginer at BI, so having issues with this one, any help would be appreciated.

I have a matrix that I have built.

In the columns are the past 13 weeks, rows show clients, and then hours are the value.

I need to add a column at the end that will calculate the movement between the first week in the matrix and the last week in the matrix.

*Note that this does not show all the data, data goes back further than 13 weeks, it is just filtered to show the most recent quarter.

 

E.g. (just using 5 weeks for sample)

 

ClientW1W2W3W4W5Movement(need help here)

Jane

14202832206
Joe7248121812(60)
Sam78787070780
Sue163040404024
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Mr_Stern 

     

    Please try this:

    Here I add your data:

    Then add a measure:

    MEASURE =
VAR _Vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[WeekNum],
        "WeekNo.", MID ( 'Table'[WeekNum], 2, 3 )
    )
VAR _diff =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        'Table'[WeekNum] = MAXX ( _Vtable, [WeekNum] )
    )
        - CALCULATE (
            SUM ( 'Table'[Hours] ),
            'Table'[WeekNum] = MINX ( _Vtable, [WeekNum] )
        )
RETURN
    IF (
        HASONEVALUE ( 'Table'[WeekNum] ),
        SUM ( 'Table'[Hours] ),
        IF ( _diff < 0, "(" & ABS ( _diff ) & ")", _diff )
    )

    Then add a matrix:

    In the format pane, change the Subtotal label in the Column subtotals:

    The result is as follow:

    Best Regards

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Mr_Stern 

     

    Please try this:

    Here I add your data:

    Then add a measure:

    MEASURE =
VAR _Vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[WeekNum],
        "WeekNo.", MID ( 'Table'[WeekNum], 2, 3 )
    )
VAR _diff =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        'Table'[WeekNum] = MAXX ( _Vtable, [WeekNum] )
    )
        - CALCULATE (
            SUM ( 'Table'[Hours] ),
            'Table'[WeekNum] = MINX ( _Vtable, [WeekNum] )
        )
RETURN
    IF (
        HASONEVALUE ( 'Table'[WeekNum] ),
        SUM ( 'Table'[Hours] ),
        IF ( _diff < 0, "(" & ABS ( _diff ) & ")", _diff )
    )

    Then add a matrix:

    In the format pane, change the Subtotal label in the Column subtotals:

    The result is as follow:

    Best Regards

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