Sort by columns returns error

[Anonymous]

Hello,

i have a table with the following columns:

• A -> POS
• B -> sales units
• C -> POS grouped (due to put together only some POS)

What I want to do is to sort the POS grouped column by sales (since I will use the POS grouped column as a slicer). However, Power BI returns me an error because there can't be more than one value in column "Sales units" for the same value in "POS grouped".

Is there a way I can do it? Before grouping, I was able to sort column A (POS) by column B (sales units).

THanks in advance.

Andy

[Anonymous]

What it is essentially saying is all the similiar instances need to have the same sort order value.  We found this magic sql a while back that sets the sort order field correctly so power bi is happy.

In our time table.. all the dates for a given month get assigned the same sort order number (Jan 2013, Feb 2013, etc).

UPDATE dbo.Time SET Month_Period_Of_Time = RowNumber
FROM dbo.Time TM, (SELECT [Month], ROW_NUMBER() OVER (ORDER BY  [Month]) AS RowNumber FROM dbo.Time) AS TMR
WHERE TM.[Month] = TMR.[Month]

[Michael-Lowden]

Is it something like this?

[Greg_Deckler]

Can you post some example/sample data? 

[Anonymous]

Hi @Greg_Deckler,

how can I attach a file in here?. 

[Greg_Deckler]

Generally people upload it to OneDrive or Box and share a link here.

[Anonymous]

@Greg_Deckler

Here is a test file.

https://drive.google.com/file/d/1-AA1Wu2hlWxeoljG1HqWD0g2lm0UhqmM/view?usp=sharing

[Greg_Deckler]

OK, for this, unless I am mistaken, you cannot use Sort By column. But, in your visual, you can use the ellipses (...) and do a sort by Sales. 

For Sort By column to work, you cannot have different values in the sorting column for the same values in the column you want to sort. For example, if for both of your Italy you had a value of 50 or a value of 20 then it would work.

[Anonymous]

Unfortunately that filter is applied to more than 20 graphs. what I put in the test file was just dummy. I need to sort the slicer by sales. Is there any way I can add a column and use some formulas like Related or sumx?

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Based my test, you should be able to follow steps below to sort the slicer(POS (groups) column) by sales.

1. Use the formula below to create a new summarize table.

Table = SUMMARIZE(Table1,Table1[POS (groups)],"Sales",SUM(Table1[SALES]))

2. Sort POS (groups) column by Sales column in the new table.

3. Create a relationship between the new summarize table and the original table.

4. Then use the POS (groups) column from new summarized table instead as slicer.

Here is the modified pbix file for your reference. 

Regards

[Anonymous]

@v-ljerr-msft amazing it works greatly!!