Getting the order i want when grouping values (buckets)

[ElioAngarita]

Hi Community! Thank you for your help as always.

My question is really simple:

I grouped values to have several buckets of aging ("b. 3 days" bucket include 2, 3 days - "c. 5 days" bucket includes 4,5 days, etc). The only reason i put a,b,c,d,e behind the name of the bucket is because if i didnt do that the system would organize them like this:

1 day

10 days

15 days

3 days

5 days

7 days

Looks like it's taking everything with number 1 first and then moving towards 2, 3 ,etc.

Is there any way i can bypass this?

[dedelman_clng · Accepted solution]

Where was Bucket in your data model before you put it on a different table? You'll want to create a relationship between the new Bucket table (column Bucket) and the table where Bucket (used to) reside.  I apologize for skipping that step.

Also make sure that the Bucket from the new table is the one in your visual.

[cadillaccc5349]

This is probably an easy fix but after adding the ranking on my visualisation, the ranking label is now displayed together with the correct x-axis label. How to resolve that? 

[dedelman_clng]

Create a separate table containing the bucket names and the rank, e.g.

Bucket     Rank

1 day       1

3 days     2

5 days     3

10 days    4

etc.

Then use the "sort by column" feature under "Column tools" to sort Bucket by Rank.

Replace Bucket in your visual with the Bucket from this new table and voila.

Hope this helps,

David

[ElioAngarita]

Thank you for your answer! Going back to the basics (i dont wanna mess with my data) how do i create that new table? it'd be a different data sheet right?

[dedelman_clng]

Yes, a different data sheet or you can use "Enter Data" and put the values in manually.

[ElioAngarita]

@dedelman_clng i created the table on another spreadsheet, got the data in the model and got rid of the letters on my buckets.

Now, the sort by column option under "Modeling" is grayed out (when i have the graph selected). Is there anything im missing?

Thank you!

[dedelman_clng]

Highlight the Bucket column in the "Fields" section, then go to Column Tools -> Sort by Column.  You are changing how the sort behavior for that column works all throughout the report, not just for that visual.  You can still control the visual sorts (ascending, descending, etc) one visual at a time.

[ElioAngarita]

@dedelman_clng back with results.

Did exactly what you suggested, i selected "Rank" on the Sort Column drop down menu. I also took the letters out of my grouping, but looks like im missing something else. My data wasn't sorted by the rank we input.

Do we need any extra step? 

[dedelman_clng · Accepted solution]

Where was Bucket in your data model before you put it on a different table? You'll want to create a relationship between the new Bucket table (column Bucket) and the table where Bucket (used to) reside.  I apologize for skipping that step.

Also make sure that the Bucket from the new table is the one in your visual.

[ElioAngarita]

@dedelman_clng, 

In one spreadsheet (data set) i have a column that calculates AGING and then another one next to it that placed that number of AGING into a category (bucket, using the IF formula). The categories are the ones stated above: 1 day, 3 days, 5 days, 7 days etc.

How do i connect the new table i created apart and the matrix table i created?

Thank you so much sir!

[dedelman_clng]

See pics below.  "Bucket" still needs to live with the fact/transactional data, so the Bucket dimension/category can be joined to it.

Transactional Data

Buckets

Model (joining "Bucket" to "Bucket"). Click on one of the "Bucket" and drag it to the other. Power BI then creates the relationship. 

Hope this helps,

David

[ElioAngarita]

Thank you! i dragged and the relation was created i guess, but still the order hasn't changed. Do i need to do anything else?

[dedelman_clng · Accepted solution]

Is Sheet1[Bucket] used in the visual?

[ElioAngarita]

IT worked! that was obvious haha.

Thank you so much kind sir.