cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Employee

## How to calculate percentile by value and frequency?

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
5 REPLIES 5
Solution Sage

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 ?

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Employee

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

Solution Sage

Oh, I missed that.

This function could be used to get those results perhaps

# 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.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Employee

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

Solution Sage

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
•

•

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.

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.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI