Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
i have a table with the following columns:
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
Solved! Go to 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
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]
Is it something like this?
Can you post some example/sample data?
Generally people upload it to OneDrive or Box and share a link here.
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |