sorting grouped bar visual

[elaj]

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 🙂

[v-zhenbw-msft]

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]

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]

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.

[elaj]

My message was not displayed... this is a test.

[elaj]

Hi,

Meanwhile I think my english is so bad that nobody understands me.
If I understand correctly, you sorted the Categorys after the distribution of OC_2, which was not my idea.

first point:
I wanted to sort the Categories in this order:
Cat_3 = 1
Cat_1 = 2
Cat_2 = 3
no matter the outcome of any data.
I know found out that you easily can make a table like this:

and make a relationship like this:

and sort Cat_lbl after sort and use it instead of Categories in the visual.
so the first problem is already solved. But only if you dont have any complicated allselected filters, which can be quite confusing when you use a field out of another table. but this i will discuss in another thread.

And second.
for my understanding we need a calculated column, which has the outcome value for Cat_3 for every OC_X like that:
(which i dont know how to create)

an then you can sort OC after sort_col descend and voila.
another approach would be to calculate another table with this:

and do the relationship with OC and sort OC_lbl after sort.
But how to calculate? i dont know 🙂

[v-zhenbw-msft · Accepted solution]

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.

[elaj]

Hi, 

Meanwhile I think my english is so bad that nobody understands me.

If I understand correctly, you sorted the Categorys after the distribution of OC_2, which was not my idea.

first point:

I wanted to sort the Categories in this order:

Cat_3	1
Cat_1	2
Cat_2	3

no matter the outcome of any data.

I know found out that you easily can make a table like this:

and make a relationship like this:

and sort Cat_lbl after sort and use it instead of Categories in the visual.

so the first problem is already solved. But only if you dont have any complicated allselected filters, which can be quite confusing when you use a field out of another table. but this i will discuss in another thread.

And second.

for my understanding we need a calculated column, which has the outcome value for Cat_3 for every OC_X like that:

(which i dont know how to create)

an then you can sort OC after sort_col descend and voila.

another approach would be to calculate another table with this:

OC_lbl	sort
OC_1	0,46
OC_2	1

and do the relationship with OC and sort OC_lbl after sort.

But how to calculate? i dont know.

[elaj]

Hi, 

Meanwhile I think my english is so bad that nobody understands me.

If I understand correctly, you sorted the Categorys after the distribution of OC_2, which was not my idea.

first point:

I wanted to sort the Categories in this order:

Cat_3	1
Cat_1	2
Cat_2	3

no matter the outcome of any data.

I know found out that you easily can make a table like this:

and make a relationship like this:

and sort Cat_lbl after sort and use it instead of Categories in the visual.

so the first problem is already solved. But only if you dont have any complicated allselected filters, which can be quite confusing when you use a field out of another table. but this i will discuss in another thread.

And second.

for my understanding we need a calculated column, which has the outcome value for Cat_3 for every OC_X like that:

(which i dont know how to create)

an then you can sort OC after sort_col descend and voila.

another approach would be to calculate another table with this:

OC_lbl	sort
OC_1	0,46
OC_2	1

and do the relationship with OC and sort OC_lbl after sort.

But how to calculate? i dont know 🙂