Forum Discussion
Difference between two groups in the same column
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
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/823601
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.
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