Percentile table like Excel

micky123

with a pleasure!

McCow There are couple of issues I am facing, please can you advise?
- Date selection doesn't affect anything, I would like to filter percentlie table based on different date ranges from stats[Date], do we need to modify the DAX?

- Cost percentile total is wrong cost_percentileC = PERCENTILEX.INC(stats,stats[Cost],[Value]) should be exctly the same as what we have in stats[Cost]?

- Sales percentile using this sales_percentileC = PERCENTILEX.INC(stats,stats[Sales],[Value]) gives me an error "Expressions that yield variant data-type cannot be used to define calculated columns."

Thanks for your time, I really appricate your help.

Kind Regards

Hi micky123,

see bellow pls

---

micky123 wrote:

McCow There are couple of issues I am facing, please can you advise?
- Date selection doesn't affect anything, I would like to filter percentlie table based on different date ranges from stats[Date], do we need to modify the DAX?

 Yes, but it is more comlex, than our example

- Cost percentile total is wrong cost_percentileC = PERCENTILEX.INC(stats,stats[Cost],[Value]) should be exctly the same as what we have in stats[Cost]?

 I maked extra page for complete table, look, summarize will be explain on picture bellow (yours [Cost] was "Don't summarise"):

- Sales percentile using this sales_percentileC = PERCENTILEX.INC(stats,stats[Sales],[Value]) gives me an error "Expressions that yield variant data-type cannot be used to define calculated columns."

observe on data type for all of yours data source, must be Decimal number, all number types can be summarized (or not):

Please check this LINK, I uploaded corrected to your request version.
Best regs.

Hi micky123,

i don't see any big problems (except summarization options for Persentille_prod[size_percentile_prod], but it' no matter).

I'll be analyse and come back later.

Hi micky123,

as you know percenttille is a statistical measue (and the DAX functions PERCENTILEX.INC and PERCENTILEX.EXC), that shows how you data set will be exactly distributed from min to max unity value (and not the SUM). The most known is the normal distribution (Gauss).

In your case, this formula will show how often it will meet this or that "size", "cost" or "sale" value. On your example, you will see that the probability of meeting of any digit more than zero, is too small (k-value from "0" to "0.99"). And you'll meet just several digits with the max value of "5" (see [sale_percentileC]) very seldom.

And all of three columns ([size_percentile_prod], [cost_percentileC] and [sale_percentileC]) are independent. They will be calculated separately without any relationships between these values in single record (line of you data set).

The second difficulty is your relationship with [size] only. But in your case it's no matter because stat_prod has about 100K records and size_prod has only 5K. I don't understand what kind of statistic you want to calculate based on size_prod only. I created relation column [_Size] in stat_prod and suggest to make all calculations on the base of the biggest table (like stat_prod in you case).

And the task of Summarization of your columns is completly another / different task.

 First version of calculation of Sum of the stats_prod table:

SumOfCost = CALCULATE(SUM(stats_prod[cost]))

The picture below shows why it is impossible to count the amount according to the distributions table:

Sorry for my English, and I hope my description provides a little more clarity to your question. If not, please ask back.

Best regs

And micky123

I'm not sure if you somehow can use it, but u can try experiment with this formula:

cost_percentileC2 = PERCENTILEX.INC(
	CALCULATETABLE(
		stats_prod;	FILTER(stats_prod;stats_prod[_Size]>0); //from min
				FILTER(stats_prod;stats_prod[_Size]<100000) //to max
		); // filter-table
	stats_prod[cost];[Value]
)//end PERCENTILEX.INC

where Filter definition create low and high (min and max) data border from your data set.

Enjoy!

micky123 , McCow ,
I can understand how to calculate Percentile as solution. But now I want to calculate Percentile and group by [size_id] in the table stats_prod. How could it possible? 
Any your help will be appriciated.