cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dgpte
Frequent Visitor

percentage along x-axis

Hi everyone. 

 

I have two columns:

Dgpte_3-1642690885721.png

I want a bar chart whit  the percentage of notes related to the number of students. ej: "50% of the id_alumno have note =8"

If we see now , my bar chart is a count of id_alumno with a note:

Dgpte_1-1642690366489.png

What is the measure that i need? 

 

Tanks!! 

 

1 ACCEPTED SOLUTION
CerebusBI
Resolver I
Resolver I

Hi,

there are several possibilities.
To illustrate them, I created some sample data (and added another column for a class or course).
Table "Notes":

CerebusBI_0-1642780658540.png

 

I see three options:

 

1. Maybe another visual is already what you need
If you choose a stacked column chart (100%), it already shows you the percentages - but all in one column. (Put the "notes" in Legend and the Quick-Measure "number of notes" in Values).

CerebusBI_1-1642780817188.png

You could also put the column "clase" in the Axis, then you get one column per class.

CerebusBI_2-1642780867252.png

 

2. Measure for percentage comparison with total number
If you want to have columns next to each other, you can use the following measure:

 

Percentage of notes =

DIVIDE (

COUNTROWS(Notes),

CALCULATE(

COUNTROWS(Notes),

REMOVEFILTERS(Notes)

)

)

With this, the visualizations above but also the following visualization is possible:

CerebusBI_3-1642781224421.png

But if you add the classes (in the Legend), the result might not be what you want:

CerebusBI_4-1642781336985.png

As you can see, the percentage is calculated to the total number of notes (rows), not to the total number of notes of the specific class.

Therefore, the third solution comes into play:


3. Measure for percentage comparison with subsets
In the previous measure, the filtered number of rows is always compared with the total number. But sometimes you want to compare only with a subset, e.g. with the total number within the class:
In that case you would have to adjust the measure and remove only the filter on the "notes" column:


Percentage of notes compared to subsets =

DIVIDE (

COUNTROWS(Notes),

CALCULATE(

COUNTROWS(Notes),

REMOVEFILTERS(Notes[notes])

)

)

 

Now, you can show the following with percentages relative to each class:

CerebusBI_5-1642781674807.png

 

I hope this helps.

 

View solution in original post

3 REPLIES 3
CerebusBI
Resolver I
Resolver I

Hi,

there are several possibilities.
To illustrate them, I created some sample data (and added another column for a class or course).
Table "Notes":

CerebusBI_0-1642780658540.png

 

I see three options:

 

1. Maybe another visual is already what you need
If you choose a stacked column chart (100%), it already shows you the percentages - but all in one column. (Put the "notes" in Legend and the Quick-Measure "number of notes" in Values).

CerebusBI_1-1642780817188.png

You could also put the column "clase" in the Axis, then you get one column per class.

CerebusBI_2-1642780867252.png

 

2. Measure for percentage comparison with total number
If you want to have columns next to each other, you can use the following measure:

 

Percentage of notes =

DIVIDE (

COUNTROWS(Notes),

CALCULATE(

COUNTROWS(Notes),

REMOVEFILTERS(Notes)

)

)

With this, the visualizations above but also the following visualization is possible:

CerebusBI_3-1642781224421.png

But if you add the classes (in the Legend), the result might not be what you want:

CerebusBI_4-1642781336985.png

As you can see, the percentage is calculated to the total number of notes (rows), not to the total number of notes of the specific class.

Therefore, the third solution comes into play:


3. Measure for percentage comparison with subsets
In the previous measure, the filtered number of rows is always compared with the total number. But sometimes you want to compare only with a subset, e.g. with the total number within the class:
In that case you would have to adjust the measure and remove only the filter on the "notes" column:


Percentage of notes compared to subsets =

DIVIDE (

COUNTROWS(Notes),

CALCULATE(

COUNTROWS(Notes),

REMOVEFILTERS(Notes[notes])

)

)

 

Now, you can show the following with percentages relative to each class:

CerebusBI_5-1642781674807.png

 

I hope this helps.

 

ValtteriN
Super User
Super User

Hi,

You did specify you wanted a bar chart, but typically when displaying this kind of data I would use a combo chart. So keep the bars as they are and use a line to display the percentages. This way you get the same information, but you don't have to deal with bucketing and overly complicated dax. For the line measure somethin like this ought to do: DIVIDE(COUNT(table[Notes]),CALCULATE(COUNT(Table[Notes]),ALL(Table))))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@Dgpte , Not very create. But you need to use binning or bucketing.

In your case, you might have to create a table with a range of each 1 %

 


Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors