How to calculate percentile by value and frequency?

[jszlm]

I have a table like below. I want to get the percentile of "Value". It equals to get the percentile of "1,1,1,1,1,2,2,2,3,3,3,3". But from the table, how can I make it?

Value  Freq

1	5
2	3
3	4

[kentyler]

Do you want to add up the values for each frequency, and then get the percent that total value is of the total for all the frequencies ?

[jszlm]

No, the purpose is to see the value number distribution.

[kentyler]

Oh, I missed that.

This function could be used to get those results perhaps

REPT

Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax

DAXCopy

REPT(<text>, <num_times>)     

I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[jszlm]

REPT() only concatenate the string. For example REPT(1,5)=11111.

[kentyler]

You can duplicate a column value and add a separator 

if you do that in a calculated column you can use CONCATENATEX() to combine the values

CONCATENATEX

• 12/09/2018
• 2 minutes to read
•  

Concatenates the result of an expression evaluated for each row in a table.

Syntax

DAXCopy

CONCATENATEX(<table>, <expression>, [delimiter])  

Parameters

Term Definition

table	The table containing the rows for which the expression will be evaluated.
expression	The expression to be evaluated for each row of the table.
delimiter	(optional) A separator to use during concatenation.

Return value

A text string.

Remarks

This function takes as its first argument a table or an expression that returns a table. The second argument is a column that contains the values you want to concatenate, or an expression that returns a value.

Example

Employees table

FirstName LastName

Alan	Brewer
Michael	Blythe

CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)

Returns "Alan Brewer, Michael Blythe"

as the example shows you can skip the first step of doing a column and do the REPT() as part of the CONCATENATEX() function.