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

[Arnaud]

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 :

.

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.

[iamprajot · Accepted solution]

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.

[iamprajot · Accepted solution]

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.

[JellyFishBi]

Many thanks.  Worked a treat.

[Arnaud]

Thank you for your reply.

I've tried both solutions you suggested.

- First solution: Here is the plot I get:

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]

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.

[Arnaud · Accepted solution]

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,

[v-chuncz-msft]

@Arnaud,

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

[Greg_Deckler]

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".

[Arnaud]

Thank you Greg for your reply.

I've tried what you suggested.

Here is the resulting plot :

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]

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.

[iamprajot]

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.