cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lidou Regular Visitor
Regular Visitor

Dax percentage

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:

VariableCountry Measure
B4B5B6France10
B4B5B6USA20
B4France2
B4USA5
B3France1
B3USA2

 

 

So I want to have this report: 

VariableCountry Measure%Measure
B4B5B6France10100%
B4B5B6USA20100%
B4France220%
B4USA525%
B3France440%
B3USA210%

 

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

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

 

VariableCountry Measure%Measure calculation
B4B5B6France10100%->10/10
B4B5B6USA20100%->20/20
B4France220%->2/10
B4USA525%->5/20
B3France440%->4/10
B3USA210%->2/20

 

 

Thank you for your help

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dax percentage

Hi @Lidou ,

 

1. Insert an index column in power query.

Capture.PNG

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

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Re: Dax percentage

@Lidou 

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!

Community Support Team
Community Support Team

Re: Dax percentage

Hi @Lidou ,

 

1. Insert an index column in power query.

Capture.PNG

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

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,223)