Forum Discussion

nikkirai's avatar
nikkirai
Frequent Visitor
3 years ago
Solved

Running Percentages DAX Power BI

Hi all,

 

I'm trying to get running percentages for this dummy dataset based on a real dataset:

 

MemberID, PortfolioID

1,1

1,2

1,3

1,4

1,5

1,6

1,7

1,8

1,9

1,10

2,1

2,2

2,3

 

I want to add another column [Running Percentages] to this dataset as below:

MemberID, PortfolioID, RunningPercentage

1,1, 10%

1,2, 20%

1,3, 30%

1,4, 40%

1,5, 50%

1,6, 60%

1,7, 70%

1,8, 80%

1,9, 90%

1,10, 100%

2,1, 33%

2,2, 66%

2,3, 100%

In SQL, there is the percentile_rank, but how do I do this in Power BI?

Thank you!

  • Try this:
     
    Measure =
    VAR _group = CALCULATE(max('Table'[MemberID]))
    VAR _max = CALCULATE(DISTINCTCOUNT ('Table'[PortfolioID]),FILTER(ALLSELECTED('Table'),'Table'[MemberID]=_group))
     RETURN RANKX(CALCULATETABLE('Table','Table'[MemberID],'Table'[PortfolioID],'Table'[MemberID]=_group),CALCULATE(sum('Table'[PortfolioID])),,ASC)/_max 
     

     

2 Replies

  • Try this:
     
    Measure =
    VAR _group = CALCULATE(max('Table'[MemberID]))
    VAR _max = CALCULATE(DISTINCTCOUNT ('Table'[PortfolioID]),FILTER(ALLSELECTED('Table'),'Table'[MemberID]=_group))
     RETURN RANKX(CALCULATETABLE('Table','Table'[MemberID],'Table'[PortfolioID],'Table'[MemberID]=_group),CALCULATE(sum('Table'[PortfolioID])),,ASC)/_max