Forum Discussion

vzbkb1's avatar
vzbkb1
Helper II
3 years ago
Solved

Total in a matrix as difference

I need to a matrix that will calculate the difference on the values on the table depending on a filter that will page in the view and where the user will be able to decide with Areas want to evaluate and see the difference

 

I will have a filter where the user will be able to choose from a list of area starting from 1 to 100.  For example if I choose in the filter the values 92 and 93, I will get a table like this

 

Area

Yellow

Red

blue

92

10

1

 

93

2

6

1

 

 

What I need to create is a measure that will calculate the difference in each area from the different colors.  EX. The result of the previous table will provide the result of

 

 

Yellow

Red

blue

Diff

8

-5

-1

 

This means the values from the smaller area (in this case 92) minus the values from the higher area (in this case 93)

 

 

Really I was thinking about the possibility of using a Matrix and substrating the rows instead of adding them but I guess that this is not possible and the solution may be to create a measure to make the calculation and put the result in a separated matrix. 

 

Can you help  getting the measure? Or other possibility to do so?

 

Thanks a lor for your help

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi vzbkb1 ,

     

    You may try this measure.

    Diff =
VAR _higher =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MAXX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
VAR _lower =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MINX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
VAR _totalhiger =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Area] = MAXX ( ALLSELECTED ( 'Table' ), [Area] )
        ),
        [Value]
    )
VAR _totallower =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Area] = MINX ( ALLSELECTED ( 'Table' ), [Area] )
        ),
        [Value]
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Area] ) || ISINSCOPE ( 'Table'[Color] ),
        IF ( ISINSCOPE ( 'Table'[Area] ), SUM ( 'Table'[Value] ), _higher - _lower ),
        _totalhiger - _totallower
    )

     

     

     

     

    Best Regards,

    Stephen Tao

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi vzbkb1 ,

     

    You may create the following measure

    Diff =
VAR _higher =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MAXX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
VAR _lower =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MINX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
RETURN
    IF ( ISINSCOPE ( 'Table'[Area] ), SUM ( 'Table'[Value] ), _lower - _higher )

    This metric is limited to the results of selecting two areas in the slicer.

     

     

    Best Regards,

    Stephen Tao

     

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

    • vzbkb1's avatar
      vzbkb1
      Helper II
      3 years ago

      The formula is working correctly, and I am getting the resutls as expect.  the onlu issue is if I include the total column into the matrix.  As you can see below, the totals per Area are OK, but in the total(difference) the Total of the las column is not correct.  Is this something that can be corrected?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi vzbkb1 ,

         

        What's your expected result with total of the last column?

         

         

         

        Best Regards,

        Stephen Tao

         

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

         

  • vzbkb1's avatar
    vzbkb1
    Helper II
    3 years ago

    Based on the table the result should be

     

    AreaBlueRedYellowTotal
    92 11011
    931629
    Total-1-58

    2

     

     

    The difference is that the total of the total column shouls be 2 (-1+ -5+8) instead of having the number 8

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi vzbkb1 ,

       

      You may try this measure.

      Diff =
VAR _higher =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MAXX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
VAR _lower =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Color] = MAX ( 'Table'[Color] )
                && [Area] = MINX ( ALLSELECTED ( 'Table' ), [Area] )
        )
    )
VAR _totalhiger =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Area] = MAXX ( ALLSELECTED ( 'Table' ), [Area] )
        ),
        [Value]
    )
VAR _totallower =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Area] = MINX ( ALLSELECTED ( 'Table' ), [Area] )
        ),
        [Value]
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Area] ) || ISINSCOPE ( 'Table'[Color] ),
        IF ( ISINSCOPE ( 'Table'[Area] ), SUM ( 'Table'[Value] ), _higher - _lower ),
        _totalhiger - _totallower
    )

       

       

       

       

      Best Regards,

      Stephen Tao

       

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

      • vzbkb1's avatar
        vzbkb1
        Helper II
        3 years ago

        It wirks perfectly.  Thanks a lot for your help