Forum Discussion
nikkirai
3 years agoFrequent Visitor
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
- andhiii079845
Solution Sage
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- nikkiraiFrequent Visitor
It works perfectly. Thank you!