cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

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

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.
1.JPG
 
 
 
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.
 
2.JPG
 
 
 
I hope I have been able to clear your doubts.
 
Please find final pbix attached.
 
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 

 

View solution in original post

Highlighted
Helper III
Helper III

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

Dear Harsh,

im sorry if somehow i misscomunicate. The final request didnt change and your way of thinking was spot on!

on your last file, i missed the EX on the percentilEX.exc and i thought that was the same, so thats why i couldnt see the difference and obviously the values in excel werent the same.

thanks a lot for your extra patience!!!

I learned a lot

VG

Highlighted
Helper III
Helper III

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

Harsh, just a quick question:

in the 2 measures bellow, you did the first one in order to respond to the filter (projct No)?

im a bit confused here, just want to understand the mechanics.

thanks

 

Total Sales by Project No = CALCULATE([Total Sales],ALLEXCEPT(Sales,Sales[Project No.]))
 
 
twentypercentile =
                       CALCULATE(
                            PERCENTILEX.EXC(Sales,
                                [Total Sales by Project No],
                                .2),ALLSELECTED(Sales[Project No.]))
Highlighted
Super User III
Super User III

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

@Vandergledison 

 

The ALLEXCEPT Calculates the Total Sales grouping the Sales with project number.

Read AllEXCEPT, Remove all FILTER except on the column mentioned.

 

Since you are calculating percentiles for the sales with project no, this needs to be the measure over which %ile calculation will be done.

 

The Twenty% is used when you filter via project no. It will calculate the %iles of the selected rows only and not the entire Sales Column.

 

Regards,

Harsh Nathani

 

 

Highlighted
Helper III
Helper III

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

This is quite interesting and i spend the whole day changing the DAW and trying to learn what would be the variations by changing some fo the paramenters. The AllEXCEPT was difficult to understand but i think that i got it how it works.

My last step, is to create Clusters, or Groups to consolidate the project Nrs.

i thought that would be very easy, i added a new filter A (for the first 25 projects), B (next 25), and so on.

the problem now, is that due to that ALLEXCEPT, if i understood correctly, it will not consider previous filters, which makes the Rank on %ile not work on that table.

in the end, i will need to Select A, and have the (e.g) 25 projects which its respective %ranking as you provided.

i know that is more than abusing but any idea on how to work this around?

thanks a lot

modified file 

Highlighted
Super User III
Super User III

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

Hi @Vandergledison ,

 

Please find 2 files attached.

 

v4 is using related and as per your grouping.

https://we.tl/t-hHaqM7OiLN

 

v2 is creating clusters based on Total Sales.

 

https://we.tl/t-MMXNCYyM7M

 

Hope it solves the issue.

 

Regards,

Harsh Nathani

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

View solution in original post

Highlighted
Helper III
Helper III

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

Thank you so much.

 

Highlighted
Helper III
Helper III

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

Hi Harsh,

The V4 worked perfectly. Im just trying to understand the mechanics and one thing i couldnt understand. You had to chreate a Column [Clusters] that relates to the [MKST] from the 'Masterdata'. why doesnt work to simply grab the [MKST] column and add in the visualization if both table are connected via a relationship.

in the future, the 'Masterdata' will be a DIM table containing several categories to act as filters. i thought that i could simly create different Filters in the futre with the categories in 'Masterdata' filtering the 'Sales' table.

rgds

VG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors