Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
How to exclude top 5% of values from the dataset using DAX?
Example: 100 line items in the grid. A Button is clicked to exclude the 5% greatest average of days(Cycle Time) line items from the dataset?
TIA,
Tanisha
Hi @Anonymous ,
Thank you @Ritaf1983 very much for the solution, , to help you understand the problem, I also tried some other ways to solve the problem:
Measure 2 = VAR rowstoexclude=ROUNDUP(COUNTROWS('Table (2)')*0.05,0)
VAR _rank =
ADDCOLUMNS('Table (2)',"Rank",RANKX(ALL('Table (2)'),[averagedays],,DESC,Dense))
RETURN SUMX(FILTER(_rank,[Rank]>rowstoexclude),[averagedays])
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,I have created this measure, But dont see any diffenece in the NSR Number count. I just have to exclude the extra NSRs (Top 5%) who have greatest Avg Days values in the dataset. By a button click these Top 5% NSRs should be excluded. Created below 2 Dax measures are not working as expected.
DAX 1:
Top 5% =
VAR _Avg =
AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ])
VAR _top =
CALCULATE (
COUNTROWS ( ADDCOLUMNS ( VALUES ( ExcludeTbl[nsrnum] ), "_Avg", _Avg ) ),
REMOVEFILTERS ( ExcludeTbl[nsrnum] )
) * 0.05
RETURN
CALCULATE (
CALCULATE ( AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ]) ),
KEEPFILTERS (
TOPN (
_top,
ALLSELECTED ( ExcludeTbl[nsrnum] ),
CALCULATE ( AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ]) ), DESC
)
)
)
TIA,
Tanisha
Hi @Anonymous ,
I'm sorry for not getting back to you until now, and I apologise for that.
If the reason it doesn't work is that it's not possible to use in measure
ROUNDUP ( COUNTROWS ( 'ExcludeTbl' ) * 0.05, 0 ) he calculates it step by step and summarises it at the end, so it will be wrong, we can use this formula to calculate the columns will solve this problem perfectly.
Rank = RANKX('CycleTimeData','CycleTimeData'[CycleTime],,DESC,Dense)
Column = ROUNDUP(COUNTROWS('CycleTimeData')*0.05,0)
Measure 2 = COALESCE(
SUMX(FILTER(
'CycleTimeData',
'CycleTimeData'[Rank]>'CycleTimeData'[Column]),
[CycleTime]),
0
)
Once again, I apologise for not resolving your issue in a timely manner.
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |