Dax percentage

[Anonymous]

Hi all,

I hope you are doing well.

I am newbie in dax query and I need your help to calculate a percentage measure.

I have a table like that:

Variable	Country	Measure
B4B5B6	France	10
B4B5B6	USA	20
B4	France	2
B4	USA	5
B3	France	1
B3	USA	2

So I want to have this report: 

Variable	Country	Measure	%Measure
B4B5B6	France	10	100%
B4B5B6	USA	20	100%
B4	France	2	20%
B4	USA	5	25%
B3	France	4	40%
B3	USA	2	10%

The [%Measure] = Measure / Measure for Variable B4B5B6.

The measure for the variable 'B4B5B6' is the denominator for the ratio [%Measure] 

Variable	Country	Measure	%Measure		calculation
B4B5B6	France	10	100%	->	10/10
B4B5B6	USA	20	100%	->	20/20
B4	France	2	20%	->	2/10
B4	USA	5	25%	->	5/20
B3	France	4	40%	->	4/10
B3	USA	2	10%	->	2/20

Thank you for your help

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

1. Insert an index column in power query.

2. To create a mueasure as below.

Per = 
VAR a =
    MAX ( 'Table'[Country ] )
RETURN
    DIVIDE (
        [measure],
        CALCULATE (
            [measure],
            FILTER ( ALL ( 'Table' ), 'Table'[Index] <= 2 && 'Table'[Country ] = a )
        )
    )

[Anonymous]

@Anonymous 

In the query editor, you can duplicate your Country column by right clicking the column header, then select the entire new column and right click and select "Replace Values..." Assign the variables to the numbers you want. It seems like for the Country France, you divide by 10 and for the Country USA, you divide by 20. So replace "France" with "10" and "USA" with "20." Make sure this column is formatted as a number (decimal number, whole number, etc.). Then you can simply create a column that divides your "Measure" row by your new row which should give you % Measure column. You can format this column as a percentage in the query editor or outside in the "Modeling" tab after selecting your new column in the Fields tab.

Hope this helps!