Horizontal bar charts

[talderton]

Hi everyone,

I have created the table shown below that shows the % of positive answers by students for each school.

I have used the following calculation in my table:

Positive response percentage of students = DIVIDE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLSELECTED(('MACSIS 2019 and 2021 survey restructured'[Positive_response]))))

(The variable 'positive response' is a simple binomal variable with the categories 'positive' and 'negative''

I now want to do the following:

Create a horizontal bar chart from the tabular data showing schools down the side rank ordered by the % positive. I also want to remove the 'positive response' column. Please see what I mean from the viz created in Tableau.

Any ideas how I do this in Power BI?

Thank you!

[talderton]

Thanks.

It didnt like this formula.

So I changed it slightly to get the correct variable names.

Now it still doenst like one aspect of it (shown in red below):

DIVIDE (CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),('MACSIS 2019 and 2021 survey restructured'[Positive response]=("Positive"), CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),('MACSIS 2019 and 2021 survey restructured'[Positive reponse]] in {"Positive","Negative"}))))))

Can you advise?

Thanks

[v-xinruzhu-msft]

Hi @talderton 

Whether you mean to show the ratio of negative types and positive types, if you need to calculate this, you can consider the following two methods

1.Put the [Positive response] into a slicer, then create the measure

Positive response percentage of students = DIVIDE (CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),'MACSIS 2019 and 2021 survey restructured'[Positive reponse]] in {"Positive","Negative"}))

2.You need to create two measures, one is positive, the other is negative. the measure can refer to the original measure I offered before. The originsl measure calculates positive type, then you need to create a new measure to calculate negative type.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[talderton]

Thanks for that.

I am getting a message that: 'The end of the input was reached' for this calculation, but nothing is showing in red so its not telling me why its failed.

[v-xinruzhu-msft]

Hi @talderton 

Can you provide some error message picture?

Best Regards!

Yolo Zhu

[talderton]

Sure, please see below

[v-xinruzhu-msft]

Hi @talderton 

Based on the message on the picture, please remove the additional "]"

Best Regards!

Yolo Zhu

[talderton]

Thanks.

I am now getting this error message:

[v-xinruzhu-msft]

Hi @talderton 

Please try the following code again

Positive response percentage of students = DIVIDE (CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students])),CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),'MACSIS 2019 and 2021 survey restructured'[Positive reponse]] in {"Positive","Negative"}))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[talderton]

No, sorry.

Just back to the e'nd of the input was reached' error message again.

[v-xinruzhu-msft]

Hi @talderton 

I made some change on it please try the following code

Positive response percentage of students = DIVIDE (CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students])),CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),'MACSIS 2019 and 2021 survey restructured'[Positive reponse] in {"Positive","Negative"}))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[talderton]

THanks very much. That now works to create the variable.

But how do I now use that to create the visualisation in my first post? i.e. with schools on the Y axis, the survey year on the X axis showing this calculated as the measure with the change between the two latest years shown in the final column?. Like this:

[v-xinruzhu-msft]

Hi @talderton 

You can use the following visual

But it cannot display the change , the default visuals power bi have offered cannot do this

Best Regards!

Yolo Zhu

[v-xinruzhu-msft]

Hi @talderton 

You can refer to the following measure

Positive response percentage of students = DIVIDE (CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),[Positive_response]="Positive"),CALCULATE (sum('MACSIS 2019 and 2021 survey restructured'[Number of students]),ALLEXCEPT('MACSIS 2019 and 2021 survey restructured','MACSIS 2019 and 2021 survey restructured'[Enumber]),[Positive_response] in {"Positive","Negative"}))

 

Then put the measure to the visual and set sort by Measure

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.