Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors