cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
great_AS Member
Member

Sort by columns returns error

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-ljerr-msft Super Contributor
Super Contributor

Re: Sort by columns returns error

Hi @great_AS,

 

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]))

t1.PNG

 

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.

 

r1.PNG

 

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

 

r2.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Sort by columns returns error

Can you post some example/sample data? 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

great_AS Member
Member

Re: Sort by columns returns error

Hi @Greg_Deckler,

 

how can I attach a file in here?. 

 

 

Super User
Super User

Re: Sort by columns returns error

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Michael-Lowden Frequent Visitor
Frequent Visitor

Re: Sort by columns returns error

Is it something like this?gNModg0

 

 

 

synergised Member
Member

Re: Sort by columns returns error

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]

 

timesort.png

 

 

great_AS Member
Member

Re: Sort by columns returns error

Super User
Super User

Re: Sort by columns returns error

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

great_AS Member
Member

Re: Sort by columns returns error

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?

Highlighted
v-ljerr-msft Super Contributor
Super Contributor

Re: Sort by columns returns error

Hi @great_AS,

 

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]))

t1.PNG

 

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.

 

r1.PNG

 

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

 

r2.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 227 members 2,349 guests
Please welcome our newest community members: