Calculate max and group the rest as n-1

[Anonymous]

Hi,

I've been asked to provide insight in how many version numbers are lower than the latest two versions. So n, n-1 and n-x. I have a data column with version numbers ranging from 10.0 to 5.0 and I want to visualise the highest version, n-1 and n-x (all the rest). Below is an example of the data:

Version	Count
10	2041
8.3	303
8.2	3
7.9	918
7.8	5
6.3	473
6.2	5398
6.1	10264
5.11	2044

What i would like to show is something like this:

Version	Count Version	Order
10	2041	N
8.3	303	N-1
<8.3	19105	N-x

I feel like this shouldn't be that hard, but I'm fairly new to powerbi and not that experienced with the Calculate function or others to get this working for me. I hope someone here will be able to help out!

Many thanks in advance!

Odi

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please kindly check the below picture, if that is what you are looking for.

I created a table that is for the purpose of grouping versions by N, N-1, N-x

The sample pbix file's link is down below, and all measures are in the file.

https://www.dropbox.com/s/xu0gz2ry3sjzusj/Odi.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Hi Jihwan Kim,

Thanks a lot! This has really helped me out. I've been able to replicate it using my full dataset and I can even expand it with n-2 or n-3 if I would want to!

I'll admit that it will take a bit of studying to really figure out what your steps are and how this has all come about the way that it does, but for now it works and that is perfect.

Greatful thanks,

Odi