tag:blogger.com,1999:blog-28148811.post1561066032978440842..comments2017-10-02T07:53:12.701+02:00Comments on Jonathan's IT Blog - Sharing Pointers: Percentile in PowerPivot DAXJoehttp://www.blogger.com/profile/16961575902853772221noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-28148811.post-64977929850603213132014-10-03T12:02:43.751+02:002014-10-03T12:02:43.751+02:00Hello Ron,
Surprisingly I had the same results th...Hello Ron,<br /><br />Surprisingly I had the same results that you described when taking my original formula as such. I worked a bit on it and came up with a few changes, to sum it up we need to make sure to remove the context filters with a couple of ALL. So here is an updated version<br /><br />=MINX(<br /> FILTER(<br /> <b>ALL(Tableau2),</b><br /> CALCULATE(COUNTROWS(Tableau2), <b>FILTER(ALL(Tableau2)</b>,Tableau2[Value] <= EARLIER(Tableau2[Value])) ) >= COUNTROWS(Tableau2)*0.9)<br /> , Tableau2[Value])<br /><br /><br />Tell me if it doesn't work for you, maybe with a sample of your data.<br /><br />JonathanJoehttps://www.blogger.com/profile/16961575902853772221noreply@blogger.comtag:blogger.com,1999:blog-28148811.post-22446620078337321212014-10-02T13:14:50.899+02:002014-10-02T13:14:50.899+02:00hi jonathan,
I found you post on generating a '...hi jonathan,<br />I found you post on generating a 'median' calc in dax.<br />ok, I am truly a newbie on powerpivot and dax. so please be patient.<br />I have 7000 rows of data, with 2 columns, a name and a number ranging from 1-100. there are NO name duplicates. in the data model:<br />- I added a 3-tier row hierarchy, and<br />- I inserted a function, and typed your formula as posted. I hit enter, it calc'd a bit, and returned all blanks.<br /><br />also, in the excel portion of the workbook, I added a pivot table. I pivot the 3-tier row hierarchy and it accurately displays levels of 'averages' of the number field. I was hoping to replace the tiered averages with tiered medians [assume that the 'median' of a data row in the lowest tier is it's same value?]<br /><br />but, blanks.<br />ok, so I added a calculated column to find a max of the data values, no problem, pastes in the max 7000 times. <br />what am I missing with minx vs simple max?<br />I clearly am doing something simple but wrong.<br /><br />thanks,<br />ronron omegnanoreply@blogger.comtag:blogger.com,1999:blog-28148811.post-7256484169373899932011-02-22T12:54:23.075+01:002011-02-22T12:54:23.075+01:00At my office they were complaining that this was n...At my office they were complaining that this was not a interpolated solution and they wanted the same results as the PERCENTILE function in excel.<br /><br />So I created 3 measures iso one<br /><br />Measure 1:<br /><br />=MINX(FILTER(VALUES(SampleData[Interger_Value]),<br />CALCULATE(COUNTROWS(SampleData),<br />SampleData[Interger_Value] <= EARLIER(SampleData[Interger_Value]) )<br />> COUNTROWS(SampleData)*[Measure 8]),<br />SampleData[Interger_Value])<br /><br />Measure 3:<br /><br />=MINX(FILTER(VALUES(SampleData[Interger_Value]),CALCULATE(COUNTROWS(SampleData),SampleData[Interger_Value] <= EARLIER(SampleData[Interger_Value]) )>(COUNTROWS(SampleData)*[Measure 8])-1),SampleData[Interger_Value])<br /><br />Measure 4:<br /><br />=MOD(COUNTROWS(SampleData)*[Measure 8],1)+(1-[Measure 8])<br /><br />If you now use Measure 4 iso your original one you got the interpolated solutionJelle-Jeroen Lamkamphttp://xlns.lamkamp.nlnoreply@blogger.comtag:blogger.com,1999:blog-28148811.post-10799949311688597672010-09-01T17:48:28.969+02:002010-09-01T17:48:28.969+02:00I would have voted for SharingMathers seeing this ...I would have voted for SharingMathers seeing this post.<br />;-)PAChttps://www.blogger.com/profile/05120308046438360437noreply@blogger.com