Hello,i have another question. my data:IDweightCategoryOCA11,96481 OC_1 11,96481 OC_2 20,58651Cat_1OC_1quoted20,58651Cat_1OC_2not quoted30,90176 OC_1 30,90176 OC_2 40,05865Cat_2OC_1quoted40,05865Cat_2OC_2quoted50,17595Cat_2OC_1not quoted50,17595Cat_2OC_2not quoted61,87683Cat_3OC_1not quoted61,87683Cat_3OC_2quoted70,17595 OC_1 70,17595 OC_2 81,85484 OC_1 81,85484 OC_2 91,11437Cat_1OC_1quoted91,11437Cat_1OC_2quoted101,81818 OC_1 101,81818 OC_2 111,56891Cat_3OC_1quoted111,56891Cat_3OC_2quoted120,72581Cat_1OC_1quoted120,72581Cat_1OC_2not quoted130,07331 OC_1 130,07331 OC_2 142,06745 OC_1 142,06745 OC_2 150,03667 OC_1 150,03667 OC_2 my visual: P OC_% W = calculate(sum(test_multi_pivot[weight]),test_multi_pivot[A] = "quoted") / calculate(sum(test_multi_pivot[weight]),test_multi_pivot[A] <> "",ALLSELECTED(test_multi_pivot[weight])) P OC_% W calculates the weighted percentage of "quoted" of A. And it is finally filtered over OC and Category inside the visual. Is it possible to sort the axis (OC_1, OC_2) after Cat_3 descend, so OC_2 would be at the first place?And is it possible to change the order of the categories to Cat_3, Cat_1, Cat_2 without creating an extra table with the sequence and connect it in the model tab or without creating an extra column in the 'test_multi_pivot' table? Thanks for your answers 🙂

Hi elaj , We can create a calculate column and a calculate table to meet your requirement. Column = VAR x = CALCULATE ( SUM ( test_multi_pivot[weight] ), FILTER ( test_multi_pivot, test_multi_pivot[A] <> "" && test_multi_pivot[Category] = "Cat_3" && test_multi_pivot[OC] = EARLIER ( test_multi_pivot[OC] ) ) ) VAR y = CALCULATE ( SUM ( test_multi_pivot[weight] ), FILTER ( test_multi_pivot, test_multi_pivot[A] = "quoted" && test_multi_pivot[Category] = "Cat_3" && test_multi_pivot[OC] = EARLIER ( test_multi_pivot[OC] ) ) ) RETURN y / x Table 2 = SUMMARIZE(test_multi_pivot,test_multi_pivot[OC],test_multi_pivot[Column]) Then you can use the [OC] column to create a relationship. If you have any question, please kindly ask here and we will try to resolve it.BTW, pbix as attached. Best regards, Community Support Team _ zhenbwIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

elaj

Helper IV

6 years ago

Solved

sorting grouped bar visual

Hello,

i have another question.

my data:

ID	weight	Category	OC	A
1	1,96481		OC_1
1	1,96481		OC_2
2	0,58651	Cat_1	OC_1	quoted
2	0,58651	Cat_1	OC_2	not quoted
3	0,90176		OC_1
3	0,90176		OC_2
4	0,05865	Cat_2	OC_1	quoted
4	0,05865	Cat_2	OC_2	quoted
5	0,17595	Cat_2	OC_1	not quoted
5	0,17595	Cat_2	OC_2	not quoted
6	1,87683	Cat_3	OC_1	not quoted
6	1,87683	Cat_3	OC_2	quoted
7	0,17595		OC_1
7	0,17595		OC_2
8	1,85484		OC_1
8	1,85484		OC_2
9	1,11437	Cat_1	OC_1	quoted
9	1,11437	Cat_1	OC_2	quoted
10	1,81818		OC_1
10	1,81818		OC_2
11	1,56891	Cat_3	OC_1	quoted
11	1,56891	Cat_3	OC_2	quoted
12	0,72581	Cat_1	OC_1	quoted
12	0,72581	Cat_1	OC_2	not quoted
13	0,07331		OC_1
13	0,07331		OC_2
14	2,06745		OC_1
14	2,06745		OC_2
15	0,03667		OC_1
15	0,03667		OC_2

my visual:

P OC_% W = calculate(sum(test_multi_pivot[weight]),test_multi_pivot[A] = "quoted") / calculate(sum(test_multi_pivot[weight]),test_multi_pivot[A] <> "",ALLSELECTED(test_multi_pivot[weight]))

P OC_% W calculates the weighted percentage of "quoted" of A. And it is finally filtered over OC and Category inside the visual.

Is it possible to sort the axis (OC_1, OC_2) after Cat_3 descend, so OC_2 would be at the first place?

And is it possible to change the order of the categories to Cat_3, Cat_1, Cat_2 without creating an extra table with the sequence and connect it in the model tab or without creating an extra column in the 'test_multi_pivot' table?

Thanks for your answers 🙂

Need Help

v-zhenbw-msft

6 years ago

Hi elaj ,

We can create a calculate column and a calculate table to meet your requirement.

Column =
VAR x =
    CALCULATE (
        SUM ( test_multi_pivot[weight] ),
        FILTER (
            test_multi_pivot,
            test_multi_pivot[A] <> ""
                && test_multi_pivot[Category] = "Cat_3"
                && test_multi_pivot[OC] = EARLIER ( test_multi_pivot[OC] )
        )
    )
VAR y =
    CALCULATE (
        SUM ( test_multi_pivot[weight] ),
        FILTER (
            test_multi_pivot,
            test_multi_pivot[A] = "quoted"
                && test_multi_pivot[Category] = "Cat_3"
                && test_multi_pivot[OC] = EARLIER ( test_multi_pivot[OC] )
        )
    )
RETURN
    y / x

Table 2 = SUMMARIZE(test_multi_pivot,test_multi_pivot[OC],test_multi_pivot[Column])

Then you can use the [OC] column to create a relationship.

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

v-zhenbw-msft
Community Support
6 years ago
Hi elaj ,

Sorry for that If do not create an auxiliary column, Power BI will not be able to sort as you wished.
Because the sort of legend is fixed, it cannot be changed by other measures.
So we need to create a calculate column to replace it, and put the category to tooltips.
You can refer the following steps,

1. Create two calculate columns to get the rank.

Column = VAR x = CALCULATE ( SUM ( test_multi_pivot[weight] ), FILTER ( ALLEXCEPT ( test_multi_pivot, test_multi_pivot[Category], test_multi_pivot[OC] ), test_multi_pivot[A] = "quoted" ) ) VAR y = CALCULATE ( SUM ( test_multi_pivot[weight] ), FILTER ( ALLSELECTED ( test_multi_pivot ), test_multi_pivot[A] <> "" && test_multi_pivot[Category] = EARLIER ( test_multi_pivot[Category] ) && test_multi_pivot[OC] = EARLIER ( test_multi_pivot[OC] ) ) ) VAR z = x / y RETURN Z

Column 2 = RANKX(FILTER(test_multi_pivot,test_multi_pivot[OC]=EARLIER(test_multi_pivot[OC])),[Column],,DESC,Dense)

2. Then put the column2 to Legend and put the category to Tooltip, the result like this,

If you have any question, please kindly ask here and we will try to resolve it.
BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- elaj
  Helper IV
  6 years ago
  Hi v-zhenbw-msft,
  
  thanks for the fast reply. Its magical what you are doing there 🙂
  
  But this is not what i wanted. I just wanted the whole OC blocks sorted after Cat_3 ascend. So that the order would be OC_2, OC_1. And then sorting the categories in the order Cat_3, Cat_1, Cat_2 in every OC_X. But having a working Legend at the same time. And every Cat_X has always the same colour. Should be easier than what you did here 🙂
  
  example here:
  
  Thanks and greetings
  elaj
  - v-zhenbw-msft
    Community Support
    6 years ago
    Hi elaj ,
    
    We can create a calculate table and a relationship to meet your requirement.
    
    1. Create a table using the following formula.
    
    Table = FILTER( ADDCOLUMNS ( SUMMARIZE ( FILTER ( test_multi_pivot, test_multi_pivot[Category] <> BLANK () ), test_multi_pivot[OC], test_multi_pivot[Category] ), "%", CALCULATE ( SUM ( test_multi_pivot[weight] ), test_multi_pivot[A] = "quoted" ) / CALCULATE ( SUM ( test_multi_pivot[weight] ), test_multi_pivot[A] <> "", ALLSELECTED ( test_multi_pivot[weight] ) ) ), [OC]="OC_2")
    
    2. Then create a column in new table,
    
    Sort_value = RANKX('Table',[%],,DESC,Dense)
    
    3. And we need to create a relationship between two tables based on Category.
    
    4. At last we put the category in new table to legend, and sort by sort_value, the result like this,
    
    If you have any question, please kindly ask here and we will try to resolve it.
    BTW, pbix as attached.
    
    Best regards,
    
    Community Support Team _ zhenbw
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.