Percentile.inc aplied to all visible rows in measure column

[Vandergledison]

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

Excel data 

Pbix file 

[zaza · Accepted solution]

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

[harshnathani · Accepted solution]

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.

https://we.tl/t-9WPKRZSWPi

Regards,

Harsh Nathani

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

[harshnathani · Accepted solution]

Hi @Vandergledison ,

Please find the pbix attached.

https://we.tl/t-gP09fMN6WE

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

[harshnathani · Accepted solution]

Hi @Vandergledison ,

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

Please find the pbix file attached.

https://we.tl/t-SZhdC34XRf

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

[harshnathani · Accepted solution]

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.

Syntax

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.

Return values SCALAR A single variant value.

PERCENTILEX.EXC

Syntax

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.

Return values SCALAR A single variant value.

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.

2. Excel Does not show the same value

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.

https://we.tl/t-jMiGnqU4dI

Regards,

Harsh Nathani

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