Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jszlm
Employee
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

15
23
34
5 REPLIES 5
kentyler
Solution Sage
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


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

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.




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


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

You can duplicate a column value and add a separator rep.PNG

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
tableThe table containing the rows for which the expression will be evaluated.
expressionThe 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
AlanBrewer
MichaelBlythe

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


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.