Forum Discussion
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 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
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
4 Replies
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601amitchandak 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.
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
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.
