Forum Discussion

elaj's avatar
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 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
Helper IV
6 years ago

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

elaj's avatar
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's avatar
    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.