Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Percentile.inc aplied to all visible rows in m...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Vandergledison

Helper III

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
05:36 AM

Hi guys,

i have a lookup table bellow related to the Net sales data table:

Project No. | Product name |

1 | A |

2 | B |

3 | C |

4 | D |

5 | E |

6 | F |

7 | G |

8 | H |

9 | I |

10 | J |

Net Sales Data:

Project No.YearNet Sales

1 | 2020 | 2.63 |

1 | 2021 | 4.5 |

1 | 2022 | 5.27 |

1 | 2023 | 6.16 |

1 | 2024 | 6.77 |

1 | 2025 | 6.77 |

1 | 2026 | 6.77 |

1 | 2027 | 6.77 |

1 | 2028 | 6.77 |

1 | 2029 | 6.77 |

2 | 2020 | 1.49 |

2 | 2021 | 3.56 |

2 | 2022 | 6.14 |

2 | 2023 | 6.37 |

2 | 2024 | 6.62 |

2 | 2025 | 6.78 |

2 | 2026 | 6.95 |

2 | 2027 | 7.12 |

2 | 2028 | 7.3 |

3 | 2023 | 6 |

3 | 2024 | 10.8 |

3 | 2025 | 10.8 |

3 | 2026 | 10.8 |

3 | 2027 | 10.8 |

3 | 2028 | 10.8 |

3 | 2029 | 10.8 |

3 | 2030 | 10.8 |

3 | 2031 | 10.8 |

3 | 2032 | 10.8 |

4 | 2021 | 0.45 |

4 | 2022 | 1.35 |

4 | 2023 | 2.25 |

4 | 2024 | 2.32 |

4 | 2025 | 2.39 |

4 | 2026 | 2.46 |

4 | 2027 | 2.53 |

4 | 2028 | 2.61 |

4 | 2029 | 2.69 |

4 | 2030 | 2.77 |

5 | 2022 | 0.45 |

5 | 2023 | 1.12 |

5 | 2024 | 2.25 |

5 | 2025 | 2.32 |

5 | 2026 | 2.39 |

5 | 2027 | 2.46 |

5 | 2028 | 2.53 |

5 | 2029 | 2.61 |

5 | 2030 | 2.69 |

5 | 2031 | 2.77 |

6 | 2023 | 0 |

6 | 2024 | 0 |

6 | 2025 | 1 |

6 | 2026 | 1 |

6 | 2027 | 1 |

6 | 2028 | 1 |

6 | 2029 | 1 |

6 | 2030 | 1 |

6 | 2031 | 1 |

6 | 2032 | 1 |

7 | 2020 | 0.64 |

7 | 2021 | 1.35 |

7 | 2022 | 1.64 |

7 | 2023 | 2.06 |

7 | 2024 | 2.76 |

7 | 2025 | 2.89 |

7 | 2026 | 3.02 |

7 | 2027 | 3.16 |

7 | 2028 | 3.32 |

7 | 2029 | 3.48 |

8 | 2020 | 1.26 |

8 | 2021 | 2.7 |

8 | 2022 | 2.76 |

8 | 2023 | 2.81 |

8 | 2024 | 2.87 |

8 | 2025 | 2.93 |

8 | 2026 | 2.99 |

8 | 2027 | 3.05 |

8 | 2028 | 3.11 |

8 | 2029 | 3.17 |

9 | 2021 | -2.4 |

9 | 2022 | 3.9 |

9 | 2023 | 6.35 |

9 | 2024 | 7.51 |

9 | 2025 | 10.22 |

9 | 2026 | 11.92 |

9 | 2027 | 15.22 |

9 | 2028 | 14.4 |

9 | 2029 | 13.61 |

9 | 2030 | 12.77 |

10 | 2020 | 6.58 |

10 | 2021 | 7.58 |

10 | 2022 | 7.1 |

10 | 2023 | 7.1 |

10 | 2024 | 7.1 |

10 | 2025 | 7.1 |

10 | 2026 | 7.1 |

10 | 2027 | 7.1 |

i would like to build a table like bellow. The Net Sales column shows the Total Sales of Product A regarding all its years of sales (long data above).

i used the measure bellow to get the 0.8 percentile:

Percentile Net sales 0.8 = PERCENTILE.INC('data for power BI porcentile'[Net Sales],0.8)

Project No. | Product name | Net Sales | Percentile Net sales 0.8 |

1 | A | 59.18 | 6.77 |

2 | B | 52.33 | 7.018 |

3 | C | 103.2 | 10.8 |

4 | D | 21.82 | 2.626 |

5 | E | 21.59 | 2.626 |

6 | F | 8 | 1 |

7 | G | 24.32 | 3.192 |

8 | H | 27.65 | 3.062 |

9 | I | 93.5 | 13.768 |

10 | J | 56.76 | 7.1 |

The problem is that i would like to have the percentile of the visible rows of this Table based on the total displayed in the Net sales column. Any ideas? thanks a lot

Solved! Go to Solution.

6 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

zaza

Resolver II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
03:21 PM

Erm... spam filter marked my message as spam, trying it again, hope this works!

Create a calculated TABLE:

```
Table =
SUMMARIZE (
'data for power BI porcentile',
'data for power BI porcentile'[Project No.],
"Net Sales", SUM ( 'data for power BI porcentile'[Net Sales] )
)
```

Then add a calculated column to it:

```
Net Sales % =
DIVIDE (
'Table'[Net Sales],
CALCULATE (
SUM ( 'Table'[Net Sales] ),
ALL ( 'Table' )
),
0
)
```

Finally create the measure:

```
80th Percentile =
IF (
ISBLANK ( MAX( 'Project Nr'[Project No.] ) ),
BLANK(),
CALCULATE (
PERCENTILE.INC ( 'Table'[Net Sales %], 0.8 ),
ALL ( 'Table' )
)
)
```

Connect it in the relationship view to get the Product name and voila:

File here: .pbix

Highlighted
**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
09:04 PM

Hi @Vandergledison ,

Sorry that i missed explain NetSales1 =

Net Sales1 = SUM('data for power BI porcentile'[Net Sales])

What i have simply done is added a table and calculate the sum of all the products and then calculated a percentile over NetSales.

Please find the pbix attached.

Regards,

Harsh Nathani

Highlighted

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-28-2020
03:26 AM

Hi @Vandergledison ,

Please find the pbix attached.

This helps in filtering and ranking your products.

Regards,

Harsh Nathani

**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

Highlighted

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-28-2020
12:17 PM

Hi @Vandergledison ,

Here you go. Hope this is what your requirement is.

Please find the pbix file attached.

You do not need the newly calculated table. There is a function in DAX for %ile calculation for expressions.

Regards,

Harsh Nathani

**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

Highlighted
## Syntax

## Return values SCALAR A single variant value.

**PERCENTILEX.EXC**

## Syntax

## Return values SCALAR A single variant value.

**2. Excel Does not show the same value** **Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-28-2020
11:24 PM

Dear @Vandergledison ,

The idea is not to confuse you. We are here to help you.

Your requirement was

1. Calculate the %ile based on Total Sales (or Sale%) for all projects.

2. Based on the %ile Calculated you wanted to Rank them i.e if the %ile is you wanted rank 5 (higher than 80p), 4 (higher than 60p), 3 (higher than 40p), 2 (higher than 20p) and 1 for the rest.

3. You also wanted the formula to Calculate the new %iles dynamically i.e if 50 projects are selected it should only Calculate the %iles of those 50 products and then Rank them based on the same criteria.

I hope my understanding is correct as your requirement has changed everytime we provided a Solution.

1. Confusion on **PERCENTILE.EXC and ****PERCENTILEX.EXC**

We were earlier calculating %iles based on a Summary Table having a row for Percentile.EXC. This same can be calculated using PercentilEX.EX where you can pass a measure instead of a Column. Please see what the formula does.

**PERCENTILE.EXC**

Returns the k-th (exclusive) percentile of values in a column.

PERCENTILE.EXC ( <Column>, <K> )

PARAMETER ATTRIBUTES DESCRIPTION Column | A column containing the values. | |

K | Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points. |

PERCENTILEX.EXC ( <Table>, <Expression>, <K> )

PARAMETER ATTRIBUTES DESCRIPTION Table ITERATOR | Table over which the Expression will be evaluated. | |

Expression ROW CONTEXT | Expression to evaluate for each row of the table. | |

K | Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points. |

So both the functions does the same thing, only difference is the parameters we pass into it.** I hope I am able to clear your doubts for this.**

The %iles which were shown in the card above were based on the filtered project no.

Let me explain this in the description below.

Below is attached image which shows the %iles based on all of your project sales. i.e Column Total Sales. Since there are lots of projects whose Sales Value is Zero, most of your %iles fall in the Zero Category.

Now below is the dynamically calculatd new %iles based on the filterd project nos i.e Total 7 column which I have filtered and it gives me the new ranks based on your criteria.

I hope I have been able to clear your doubts.

Please find final pbix attached.

Regards,

Harsh Nathani

Highlighted

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-30-2020
01:27 PM

Hi @Vandergledison ,

Please find 2 files attached.

v4 is using related and as per your grouping.

v2 is creating clusters based on Total Sales.

Hope it solves the issue.

Regards,

Harsh Nathani

**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

37 REPLIES 37

Highlighted
##

harshnathani

Super User V

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
07:18 AM

Hi @Vandergledison ,

Do you mean you want to show the percentile of the netsales?

If this is the case, all your products will have percentile equal to the net sales, as there is only one entry for each of the net sales of the product and the row context is of the product.

Incase you want a card to show you .8 percentile of the netsales the value comes out to be 86.64.

You can create a Summary Table:

Table = SUMMARIZE('data for power BI porcentile','Project Nr'[Product name],"Sales",[Sum of Product Name])

Q0.8 = PERCENTILE.EXC('Table'[Sales],0.8)

Regards,

Harsh Nathani

**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

**In case it did not help, please provide additional information and mark me with @ so that I can investigate further**

Highlighted
##

Vandergledison

Helper III

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
07:38 AM

Hi Harsh, the sales i have in the table are the total Sales for each product. This values came from another table containing the sales per year. what i need now is a new measure that provides me the percentile 80% of each product but now based on the total net sales as you see bellow

Project No. | Product name | Total Net Sales |

1 | A | 59.18 |

2 | B | 52.33 |

3 | C | 103.2 |

4 | D | 21.82 |

5 | E | 21.59 |

6 | F | 8 |

7 | G | 24.32 |

8 | H | 27.65 |

9 | I | 93.5 |

10 | J | 56.76 |

The values for each product will be the same. What i want to do in the end is to Rank each product. I want to have in each row an if function that will score each product regarding its location within a certain percerntile. for example if product A has total sales higher than the percentile 80% it should get a 5, if its higher than 60% and lower than 80%, it should get a 4, and so on..

i hope its more clear.

thanks

Highlighted
##

Vandergledison

Helper III

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
09:29 AM

basically i would like to do like in EXCEL.

in excell i applied the formula: =PERCENTILE.INC($D$2:$D$11,0.8)

in column E is showing the result. i just want the same in power BI via some measure.

A | B | C | D | E |

Project No. | Product name | Net Sales | % of total net sales | 80% Percentile from % of total net sales |

1 | A | 59.18 | 13% | 14% |

2 | B | 52.33 | 11% | 14% |

3 | C | 103.2 | 22% | 14% |

4 | D | 21.82 | 5% | 14% |

5 | E | 21.59 | 5% | 14% |

6 | F | 8 | 2% | 14% |

7 | G | 24.32 | 5% | 14% |

8 | H | 27.65 | 6% | 14% |

9 | I | 93.5 | 20% | 14% |

10 | J | 56.76 | 12% | 14% |

Highlighted
##

harshnathani

Super User V

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
10:12 AM

Hi @Vandergledison ,

**Create a table.**

Table = ADDCOLUMNS(

ADDCOLUMNS(

SUMMARIZE('data for power BI porcentile','data for power BI porcentile'[Project No.],'Project Nr'[Product name]),

"ns",[Net Sales1],

"%tot",CALCULATE([Net Sales1],ALL('data for power BI porcentile'))),

"%%tot", [ns]/[%tot])

Create a Calculated Column in the table

Create a Calculated Column in the table

Per = PERCENTILE.INC('Table'[%%tot],.8)

You will get a table created like

Regards,

Harsh Nathani

**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

**In case it did not help, please provide additional information and mark me with @ so that I can investigate further**

Highlighted
##

Vandergledison

Helper III

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
01:20 PM

Hi,

thanks a lot, im still cracking my head to understand what you did (sorry, very new to Power BI).

somehow, when i tried to select the [Net Sales] from the table 'data for power BI porcentile' it doesnt work, it only shows the availible measures.

by just copy pasting what you have sent me, it doesnt find the [Net Sales1] i tried to change to [Net Sales] and also didnt work.

thanks a lot

Highlighted
##

zaza

Resolver II

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
01:43 PM

There might be a better way to do this, but this should work:

```
Measure =
VAR __totals =
CALCULATE (
SUM ( 'data for power BI porcentile'[Net Sales] ),
ALL ( 'Project Nr' )
)
RETURN
DIVIDE ( SUM ( 'data for power BI porcentile'[Net Sales] ), __totals, 0 )
```

Highlighted
##

Vandergledison

Helper III

Re: Percentile.inc aplied to all visible rows in measure column

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
01:49 PM

hi,

The percentile value you have per product refers to the 5 or more years that are counted for each product. what i need is the percentile based on the total net sales as presented in the visual table.

thanks

Highlighted

zaza

Resolver II

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
03:21 PM

Erm... spam filter marked my message as spam, trying it again, hope this works!

Create a calculated TABLE:

```
Table =
SUMMARIZE (
'data for power BI porcentile',
'data for power BI porcentile'[Project No.],
"Net Sales", SUM ( 'data for power BI porcentile'[Net Sales] )
)
```

Then add a calculated column to it:

```
Net Sales % =
DIVIDE (
'Table'[Net Sales],
CALCULATE (
SUM ( 'Table'[Net Sales] ),
ALL ( 'Table' )
),
0
)
```

Finally create the measure:

```
80th Percentile =
IF (
ISBLANK ( MAX( 'Project Nr'[Project No.] ) ),
BLANK(),
CALCULATE (
PERCENTILE.INC ( 'Table'[Net Sales %], 0.8 ),
ALL ( 'Table' )
)
)
```

Connect it in the relationship view to get the Product name and voila:

File here: .pbix

Highlighted
**Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!**

harshnathani

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-26-2020
09:04 PM

Hi @Vandergledison ,

Sorry that i missed explain NetSales1 =

Net Sales1 = SUM('data for power BI porcentile'[Net Sales])

What i have simply done is added a table and calculate the sum of all the products and then calculated a percentile over NetSales.

Please find the pbix attached.

Regards,

Harsh Nathani

Top Solution Authors

User | Count |
---|---|

53 | |

50 | |

18 | |

18 | |

14 |

Top Kudoed Authors

User | Count |
---|---|

73 | |

66 | |

34 | |

31 | |

23 |