Issue with stacked bar chart splitting incorrectly

[BoogieMan_01]

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 for everything to be evenly split like this. 

My whole page is already filtered by Undergraduate and Postgraduate, but that shouldn't be the issue. 

Currently, my DAX formulas are: 

Q12 Responses = DISTINCTCOUNT(Q12[ResponseId])

Q12 Responses Finished = CALCULATE([Q12 Responses],Q12[Finished]=true,ALLSELECTED(Q12[Q12]))

Q12 Total Responses = DIVIDE([Q12 Responses],[Q12 Responses Finished])

and on y-axis I have Q12 Total Responses and study type (Undergraduate and Postgraduate) on the x-axis. 

This is a snippet of my table values and I have attached a ful spreadsheet of the data:

Survey Data.xlsx

Any help would be appreciated!

[techies · Accepted solution]

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]

Hi ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, we kindly request you to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If our response was helpful, please mark it as Accepted as Solution and consider giving a Kudos. Feel free to reach out if you need any further assistance.

Best Regards,

Prashanth Are

[v-prasare]

@BoogieMan_01, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

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

[v-prasare]

Hi @BoogieMan_01 
Thanks for actively participating in MS Fabric community support

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

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

[v-prasare]

Hi @BoogieMan_01,

Thanks for actively participating in MS Fabric community support

If the solution works please do accept it as a solution so as it will be easier for others to find

Thanks In Advance

Prashanth

[techies]

is this what you needed ?

[BoogieMan_01]

Oh! @techies it worked! I have the same output as you but now the issue is that I want to arrange the legend so that it's: 

Yes definity -> yes probably -> no probably not -> no definity not

So I created another table called rating and made a one-to-many relationship with the main table: 

Then I sorted rating by column index and put that in the legend so that it would be in the order I wanted. Is there another way to customer order my graph?

[techies]

Hi @BoogieMan_01 you can sort by clicking the 3 dots 

[BoogieMan_01]

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

[v-prasare · Accepted solution]

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]

ok

[BoogieMan_01]

Hi @techies 

Yes! That is what I need! Did you get that from using the your previous DAX formula?

[adudani]

hi @BoogieMan_01 ,

An alternative to sort by columns reference:Never do Sort by Column Again! MUST WATCH 🔥

The solution using DAX to add non width spaces. 

[techies · Accepted solution]

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])

    )

)

[BoogieMan_01]

Hi @techies ! 

Thank you for the reply but unfortunately it did not work... It's still showing the same graph