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

[sarthakgirdhar]

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

[SachinNandanwar]

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

[sarthakgirdhar · Accepted solution]

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)

[Selva-Salimi]

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. 

[sarthakgirdhar]

Hi @Selva-Salimi,

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

Thanks a lot for your attempt though 🙂

[Selva-Salimi]

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)

[sarthakgirdhar]

This is what it looks like:-

% Test3 is the measure.

[Selva-Salimi]

@sarthakgirdhar 

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

[sarthakgirdhar]

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

)