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
Arnaud
Frequent Visitor

Looking for a DAX formula to calculate percentage per category, per year

Hi all,

 

I'm new to Power BI.

For my school, I'm trying to plot curves that show the percentage of students coming from the different regions of France, over the years.

From my main table, which contains one row per student, I could compute the number of students coming from these regions over the years, by using this DAX formula :

Number Per Region = SUMX(
    KEEPFILTERS(VALUES('Donnees-SCOL'[Annee]));
    CALCULATE(COUNT('Donnees-SCOL'[Departement_dernier_diplome])))

Where [Annee] is the year; [Departement_dernier_diplome] is the region of France.

The resulting plot looks like :

Example.JPG.

 

But I did not manage to calculate, for each year, the corresponding percentage of students coming from these regions

For a given year, I would expect something like : Percentage Per Region in Year = Number Per Region / TOTAL Number in Year.

 

Can anybody help me to find out the right DAX formula?

 

Thank you in advance,

Arnaud.

2 ACCEPTED SOLUTIONS
iamprajot
Responsive Resident
Responsive Resident

There are 2 options to achive that:-

1. Measure = COUNTA(TableName[Region])/CALCULATE(COUNTA(TableName[Region]),ALLSELECTED(TableName[Region]))

& Format it to %

 

2. Create Matrix Chart, Put Year in Rows, Region in Columns, Put Region again in Values (COUNT) and Select show value as Percent of Row Total

 

and finally convert Matrix Chart into Line Chart accordingly.

View solution in original post

Hi again,

 

I worked on your solution 1 and actually modified the DAX formula you suggested, as follows:

Percentage Per Region = DISTINCTCOUNT('Donnees-SCOL'[ID])/
CALCULATE(DISTINCTCOUNT('Donnees-SCOL'[ID]);ALLSELECTED('Donnees-SCOL'[REGION]))

where ID is the student's ID; REGION is the region of France.

In fact, I had to make appear the number of "distinct" students somewhere in the formula.

I now obtain the plot I was looking for.

Thank you for your help: I did not know the function ALLSELECTED.

 

Best,

View solution in original post

10 REPLIES 10
iamprajot
Responsive Resident
Responsive Resident

There are 2 options to achive that:-

1. Measure = COUNTA(TableName[Region])/CALCULATE(COUNTA(TableName[Region]),ALLSELECTED(TableName[Region]))

& Format it to %

 

2. Create Matrix Chart, Put Year in Rows, Region in Columns, Put Region again in Values (COUNT) and Select show value as Percent of Row Total

 

and finally convert Matrix Chart into Line Chart accordingly.

Many thanks.  Worked a treat.

Thank you for your reply.

 

I've tried both solutions you suggested.

 

- First solution: Here is the plot I get:

Example.JPG

Something's going wrong. Shouldn't the column [Annee] appear somewhere in the formula you suggested?

 

- Second solution:

Everything's going well until I'm trying to convert Matrix Chart into Line Chart. The plot looks like exactly the one I've just posted in my reply to Greg.

 

I'm a bit lost. If you have any further suggestion ...

 

Thank you in advance for your help,

Best.

iamprajot
Responsive Resident
Responsive Resident

Put columns in Line chart correctly, or share a screenshot of where did you put the dimentions/columns in the chart.

Also change it to % first as it does not look like % and hence straight line at 1.

Hi again,

 

I worked on your solution 1 and actually modified the DAX formula you suggested, as follows:

Percentage Per Region = DISTINCTCOUNT('Donnees-SCOL'[ID])/
CALCULATE(DISTINCTCOUNT('Donnees-SCOL'[ID]);ALLSELECTED('Donnees-SCOL'[REGION]))

where ID is the student's ID; REGION is the region of France.

In fact, I had to make appear the number of "distinct" students somewhere in the formula.

I now obtain the plot I was looking for.

Thank you for your help: I did not know the function ALLSELECTED.

 

Best,

@Arnaud,

 

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

Click on that visualization. Then, notice the little arrow for that measure/column in the Values area of your VISUALIZATIONS pane. Click that and then choose "Show value as | Percent of grand total".

 

image.png


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg for your reply.

I've tried what you suggested.

Here is the resulting plot :

Example.JPG

An issue occurs: the percentage is abnormally low; in fact, it is computed with respect to the grnad total. I would like it to be computed with respect to the total in a given year.

Any further idea? 🙂

 

Thank you in advance for your help,

Best,

iamprajot
Responsive Resident
Responsive Resident

It will be low as it is Percent of Grand Total, what you should do is create a Matrix Table first and then choose Percent of Row Total and then transform it into a Line Chart.

Hi @Greg_Deckler Percent of Grand Total is giving result based on all the years as a Total and for year wise percentage it should be Percent of Row Total, which is coming by going through Matrix Table first.

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.