Forum Discussion

BoogieMan_01's avatar
BoogieMan_01
Frequent Visitor
1 year ago
Solved

Issue with stacked bar chart splitting incorrectly

Hi everyone,    I have created a survey and for a question, particpants has to rate their expereince among 4 options. Currently, this is my chart but this is extremely wrong as it's not possible fo...
Need Help
  • techies's avatar
    techies
    1 year ago

    Hi BoogieMan_01 please try this

     

    Q12 Finished Responses =
    CALCULATE(
        [Q12 Responses],
        FILTER(Q12, Q12[Finished] = TRUE())
    )
     
    Q12 Response % by Option =
    DIVIDE(
        [Q12 Finished Responses],
        CALCULATE(
            [Q12 Finished Responses],
            ALLSELECTED(Q12[Q12])
        )
    )
  • v-prasare's avatar
    v-prasare
    1 year ago

    Hi BoogieMan_01,

     

    To properly control the order of your legend in Power BI, avoid using the raw response column (Q12[Q12]) directly. Instead, create a separate dimension table that lists all possible ratings along with a corresponding numerical index representing their intended order. This index can then be used to sort the labels correctly. By referencing this dimension table in your visual instead of the original column, Power BI will be able to respect the sort order you define.

     

    create this custom Rating table using the following DAX expression:

    Rating = DATATABLE(
    "Rating", STRING,
    "Index", INTEGER,
    {
        {"Yes definitely", 1},
        {"Yes probably", 2},
        {"No probably not", 3},
        {"No definitely not", 4}
    }
)

     

    After creating the table, go to the Data view, select the Rating column, and apply “Sort by Column” using the Index column. This step ensures that the ratings appear in your desired custom order when used in visuals.

     

    Next, set up a one-to-many relationship from Rating[Rating] to your main data table Q12[Q12]. This allows each response to map to a specific, sorted rating. Finally, use Rating[Rating] (not Q12[Q12]) in the legend field of your visual. This will enforce the proper order you've defined, giving you full control over how the survey responses are displayed.

     

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

     

techies's avatar
techies
Super User
1 year ago

Hi BoogieMan_01 you can sort by clicking the 3 dots 

 

 

BoogieMan_01's avatar
BoogieMan_01
Frequent Visitor
1 year ago

Hey techies adudani 

 

1. for the YT video, it was interesting! But ended up filling my value column with errors

2. sort legend doesn't order the values to yes definity -> yes probably -> no probably not -> no definity not, instead it sorts by descending order. So it'll be yes probably -> yes definitely-> no probably not -> no definity not. So my issue still remains

  • techies's avatar
    techies
    Super User
    1 year ago

    ok

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    Hi BoogieMan_01,

     

    To properly control the order of your legend in Power BI, avoid using the raw response column (Q12[Q12]) directly. Instead, create a separate dimension table that lists all possible ratings along with a corresponding numerical index representing their intended order. This index can then be used to sort the labels correctly. By referencing this dimension table in your visual instead of the original column, Power BI will be able to respect the sort order you define.

     

    create this custom Rating table using the following DAX expression:

    Rating = DATATABLE(
    "Rating", STRING,
    "Index", INTEGER,
    {
        {"Yes definitely", 1},
        {"Yes probably", 2},
        {"No probably not", 3},
        {"No definitely not", 4}
    }
)

     

    After creating the table, go to the Data view, select the Rating column, and apply “Sort by Column” using the Index column. This step ensures that the ratings appear in your desired custom order when used in visuals.

     

    Next, set up a one-to-many relationship from Rating[Rating] to your main data table Q12[Q12]. This allows each response to map to a specific, sorted rating. Finally, use Rating[Rating] (not Q12[Q12]) in the legend field of your visual. This will enforce the proper order you've defined, giving you full control over how the survey responses are displayed.

     

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query