topic Re: Need help calculating percentage of an individual value within its group (Power BI, DAX) in DAX Commands and Tips

Need help calculating percentage of an individual value within its group (Power BI, DAX)

sarthakgirdhar — Mon, 23 Sep 2024 16:01:27 GMT

Hi DAX gurus,

I need help writing a measure. The sample data is as shown below:-

I wish to find the percentage of each country within the group "International" every year.

For example, India in year 2020 = 50%; USA in year 2020 = 50%. India in year 2021 = 100%. Etc.

This is what I have written, however, this doesn't work:-

DIVIDE (

COUNT ( 'Data'[Country] ),

COUNTROWS( FILTER ('Data', 'Data'[Country (group)] = "International") )

)

I believe the denominator is correct. I think the numerator needs some help.

Many thanks,
Sarthak

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

Selva-Salimi — Mon, 23 Sep 2024 16:30:07 GMT

Hi sarthakgirdhar

I think every row in your data play role so you just need to devide "one" by calculating the denaminator. you can write a measure as follows:

Measure_share := 1 / calculate (distinctcount(country) , filter (all(your_table) , year=selectedvalue(year) && country(group) = "International")

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

sarthakgirdhar — Mon, 23 Sep 2024 17:05:02 GMT

Hi Selva-Salimi,

I tried your solution, however, it doesn't give the desired output.

Thanks a lot for your attempt though 🙂

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

Selva-Salimi — Mon, 23 Sep 2024 17:06:48 GMT

So, let me know what was the issue and also what are the visuals you use to display this measure ( image of sample if possible)

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

sarthakgirdhar — Mon, 23 Sep 2024 17:53:35 GMT

This is what it looks like:-

% Test3 is the measure.

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

Selva-Salimi — Mon, 23 Sep 2024 18:02:26 GMT

sarthakgirdhar

Are they the only international countries in 2020? is the column academic_year used in selectedvalue function in measure?!

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

sarthakgirdhar — Mon, 23 Sep 2024 18:15:52 GMT

There are about 100 International countries every year. The years go from 2020 to 2024.

I wrote the measure as per your post:-

% Test3 = 1 / CALCULATE(

distinctcount('Data'[country_description]) , filter (all('Data') , selectedvalue('Data'[academic_year]) && 'Data'[country_description (groups)] = "International")

)

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

SachinNandanwar — Mon, 23 Sep 2024 18:26:29 GMT

This ?

Country_Percentage_International = VAR _GroupCount = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Year] ), 'Table'[Group] = "International" ) ) RETURN DIVIDE ( COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[StudentId], 'Table'[Group] ), 'Table'[Group] = "International" ) ), _GroupCount, 0 ) * 100

this the data

StudentId,Country,Group,Year
1,India,Domestic,2020
2,USA,International,2021
3,Canada,International,2022
4,India,Domestic,2023
5,USA,International,2020
6,Canada,International,2021
7,India,Domestic,2022
8,USA,International,2023
9,Canada,International,2020
10,India,International,2021
11,USA,International,2021

Re: Need help calculating percentage of an individual value within its group (Power BI, DAX)

sarthakgirdhar — Mon, 23 Sep 2024 18:50:46 GMT

Hi SachinNandanwar,

Thank you so very much for your response!

This isn't completely the correct response, however, I was able to write it with your help:-

% of Country in International =

var intl_country_every_year = CALCULATE (

COUNT('Data'[country_description]),

FILTER (

ALLEXCEPT('Data', 'Data'[academic_year]),

'Data'[country_description (groups)] = "International"

)

return DIVIDE(COUNT('Data'[country_description]), intl_country_every_year)