Difference between two groups in the same column

[Anonymous]

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 value in [Value]. Now, I need to use DAX to create a formula column capable of calculating the slope between each represented group. It should work like this:

((Value of Group)-(Value of next lowest Group)) / ((Group)-(Next Lowest Group))

Ex: (55-27)/(7-4) = 28/3 = 9.3

     (27-34)/(4-3) = -7/1 = -7

[v-eachen-msft]

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.

[amitchandak]

Create a dense rank on group

Follow: https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

and the filter on each rank and take a max

divide(maxx(filter(table,rank=4),value),maxx(filter(table,rank=3),value))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak I appreciate the suggestion, but this did not work for me. The ranking solution in the article did not provide consistent ranking (ranks jump from 3 to 7 or from 14 to 19) instead of 1, 2, 3, etc. Additionally, there are so many ranks for the groups that the formula provided would not work. I would need to know if there is a way to use DAX to automatically grab the next lowest/highest rank, regardless of the difference between them.

[amitchandak · Accepted solution]

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