Forum Discussion

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 qu...

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.

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.

elaj
Helper IV
6 years ago
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
Helper IV
6 years ago
My message was not displayed... this is a test.
- elaj
  Helper IV
  6 years ago
  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
  Helper IV
  6 years ago
  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
    Community Support
    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.

Forum Discussion

sorting grouped bar visual

Related content

Matrix Visual Sorting Issue

Sorting matrix visual

Table Visual Sorting

Sorting

Fabric & Azure Data User Group Program Requirements & Benefits