Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference between two groups in the same column

I have data similar to the following   Group Value 1 25 1 25 3 34 3 34 3 34 4 27 4 27 7 55 7 55   Essentially, every value in [Group] has the same v...
Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    I created a rank column and a measure like given below

     

    Column = RANKX(Sheet1,Sheet1[Group],,DESC,Dense)
Measure = divide(maxx(FILTER(Sheet1,Sheet1[Column]=1),Sheet1[Value])-maxx(FILTER(Sheet1,Sheet1[Column]=2),Sheet1[Value]),maxx(FILTER(Sheet1,Sheet1[Column]=1),Sheet1[Group])-maxx(FILTER(Sheet1,Sheet1[Column]=2),Sheet1[Group]))

     

    You can create one more for the second. Unless you were looking for some other display.

     

    Link : https://www.dropbox.com/s/p3ewc9h970503qh/groupRank.pbix?dl=0

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous ,

 

Since each group has the same value, you could create a new table to show single group and value.

Table 2 =
DISTINCT (
    SELECTCOLUMNS ( 'Table', "C1", 'Table'[Group], "C2", 'Table'[Value] )
)

Then create an index column.

Column =
RANKX ( 'Table 2', 'Table 2'[C1],, ASC, DENSE )

Use function to get the value.

Column 2 =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table 2'[C1], 1 ),
        FILTER ( 'Table 2', 'Table 2'[Column] = EARLIER ( 'Table 2'[Column] ) - 1 )
    )
VAR b =
    CALCULATE (
        FIRSTNONBLANK ( 'Table 2'[C2], 1 ),
        FILTER ( 'Table 2', 'Table 2'[Column] = EARLIER ( 'Table 2'[Column] ) - 1 )
    )
RETURN
    ROUND ( ( 'Table 2'[C2] - b ) / ( 'Table 2'[C1] - a ), 1 )

 Here is my test file for your reference.