Forum Discussion

elaj's avatar
elaj
Icon for Helper IV rankHelper 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's avatar
    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's avatar
elaj
Icon for Helper IV rankHelper 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's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity 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.