Ranking a parameter on filtered grouped table
Hi! I'm having trouble writing a measure that ranks a parameter. Essentially, the user inputs a numeric value as a parameter and my report should output it's ranking based on grouped sales by week and client. Filters can be applied. To make this exercise easier I have attached a picture of my report page and a sample PBI file. The column highlighted in red is the one NOT working properly. Would appreciate any tips on how to modify it. Since my parameter might not exist in my grouped table, I first created a measure that will return the closest value in order to then calculate the ranking. This works fine. Closest amount to parameter = var threshold = Parameter[Parameter Value] ---- returns parameter inputed by the user VAR SourceTable = ADDCOLUMNS ( ALLSELECTED ( Sales[Weeknum + ClientID]), "@Amt", [Sales] ) ----- temp table adding sales amount, grouped by weeknum and client var closest_above = MINX(FILTER(SourceTable, [@Amt]>= threshold), [@Amt]) var closest_below = MAXX(FILTER(SourceTable, [@Amt]<= threshold), [@Amt]) var result = if(abs(threshold-closest_above) < abs(threshold-closest_below), closest_above, closest_below) return result Second measure should return the rank of my parameter based on grouped table. Issue is that its not taking in account slicer filters applied to the report page. Essentially works fine until I select a filter in the slicer 'TransDesc'. Rank of parameter = var SourceTable = ADDCOLUMNS( ALLSELECTED ( Sales[Weeknum + ClientID]), "@Amt", [Sales]) var GroupedTable = ADDCOLUMNS(FILTER(SourceTable, [@Amt] <> BLANK()), "@Rank", RANKX(SourceTable, [Sales],,ASC)) var threshold = [Closest amount to parameter] var result = MINX(FILTER(GroupedTable, [@Amt]= threshold), [@Rank]) return result In example below, I would expect that Rank of parameter = 136 here is the link: RankingSample.pbix Any help is appreciated!
RANKX with additional conditions
I'm trying to calculate an agency's ranking using the RANKX formula, but I can't figure it out. What I am trying to do is for the ranking to be calculated taking into account the investment of each agency, but this cannot consider agencies with negative YoY, they must be excluded from the ranking. I only want a ranking of Agencies that have a positive % variation (2023 vs 2024). I am using the following formula but without much success. RANKX Agencia 1 Pos = VAR InversionYTD = [Inversión neta YTD Agencia] VAR VariacionYTD = [Variación YTD Agencia] RETURN IF( NOT ISBLANK(InversionYTD) && InversionYTD <> 0 && VariacionYTD > 0, RANKX( FILTER( ALLSELECTED('Industria'[Agencia]), [Variación YTD Agencia] > 0 ), CALCULATE( [Inversión neta YTD Agencia], REMOVEFILTERS('Industria'[Anunciante]), -- Delete filters of advertiser column ), , DESC, Dense ), BLANK() ) The expected result would be a matrix visual object like the one in the image. Expected result Anunciante Agencia 2023 2024 Diff YoY Rankx TECNOQUIMICAS SANCHO/BBDO $ 364,529 $ 435,109 $ 70,580 19% 1 CENTRAL CERVECERA SANCHO/BBDO $ 180 $ 32,094 $ 31,915 17744% 1 PEPSI COL+POSTOBON SANCHO/BBDO $ 3,856 $ 20,385 $ 16,529 429% 1 ALMACENES EXITO SANCHO/BBDO $ 65,194 $ 79,979 $ 14,785 23% 1 BANCOLOMBIA SANCHO/BBDO $ 45,218 $ 59,528 $ 14,309 32% 1 ALIMENTOS POLAR SANCHO/BBDO $ 14,610 $ 26,580 $ 11,970 82% 1 MERCADOLIBRE.COM SANCHO/BBDO $ 14,966 $ 24,951 $ 9,986 67% 1 PEPSICO ALIMENTOS SANCHO/BBDO $ 11,078 $ 17,517 $ 6,439 58% 1 ECOPETROL SANCHO/BBDO $ 8,340 $ 12,604 $ 4,264 51% 1 LEADERS+CEET SANCHO/BBDO $ 2,234 $ 5,461 $ 3,226 144% 1 C FUNCIONAL BEBIDAS SANCHO/BBDO $ 965 $ 3,937 $ 2,972 308% 1 DAIMLER COLOMBIA SA SANCHO/BBDO $ 2,167 $ 5,033 $ 2,865 132% 1 TERPEL SANCHO/BBDO $ 1,763 $ 4,196 $ 2,433 138% 1 AVON CALLINGS SANCHO/BBDO $ 268 $ 1,493 $ 1,225 456% 1 ARTURO CALLE SANCHO/BBDO $ 301 $ 1,380 $ 1,079 359% 1 CORONA SANCHO/BBDO $ 2,397 $ 2,782 $ 385 16% 1 L&C S.A.S SANCHO/BBDO $ 892 $ 1,238 $ 346 39% 1 PJ COL SAS SANCHO/BBDO $ 478 $ 812 $ 334 70% 1 ALM EXITO+B COLPAT SANCHO/BBDO $ 83 $ 230 $ 147 176% 1 COLGATE PALMOLIVE YOUNG & RUBICAM $ 159,954 $ 257,348 $ 97,394 61% 2 COLGATE PALMOLIV INT YOUNG & RUBICAM $ 9,363 $ 31,787 $ 22,424 240% 2 HUAWEI TECHNOLOGIES YOUNG & RUBICAM $ 541 $ 3,313 $ 2,772 512% 2 DERCO SA YOUNG & RUBICAM $ 3,175 $ 4,147 $ 972 31% 2 WALT DISNEY COMPANY BTL $ 145,513 $ 254,319 $ 108,806 75% 3 I have the following table
Rank basing on measure with parameter & Rank percentile
Hi Everyone, 1) I have created following measure in DAX: Model score weighted FA Table = [Productivity FA table Value] / 100 * AVERAGE(act_source[team_prod_model_score]) + (100 - [Productivity FA table Value])/100 * AVERAGE(act_source[fa_prod_model_score]) Where [Productivity FA table Value] is defined by What-If parameter as following: Productivity FA table = GENERATESERIES(0, 100, 50) Now I am strugling to prepare rank field using above measure. Basically I am trying to find equivalent of Rank_Unique() function in Tableau, which look like as follows: 2) Similar question related to ranking. I have field in my data called [surv_model_score]. I want to apply almost exactly same formula of Rank_Unique as above. Additionaly, on the output, I want to apply equivalent of Rank_Percentile() function as follows: Is there any way to get equivalent of rank_percentile in my case? Thanks and Merry Christmas !Consistent Ranking Measure Ignoring Page Slicers
I am trying to create a ranking measure in Power BI based on a specific variable while ensuring that the ranking remains consistent regardless of any slicers applied on the page. But the ranking changes when the slicer is applied. The ranking should remain the same irrespective of any slicers applied on the page (specifically, slicers for Category). I have tried ALL and ALLSELECTED but the rankings change when I applied the slicers. Here is the measure I used: rank = RANKX(ALL(Table),CALCULATE(SUM(Table[Income]),REMOVEFILTERS(Table[Category])),,DESC,Dense) When I apply the slicer, the ranking position decreases by one spot with this measure. How can I change this for it to work?
Top Ranked in various metrics
I have a number of current ranking measures which detail top salesperson, top selling region etc which are working well within a clustered column visual. What I would like to do with each metric is display some additional information. So in best selling region, I'd like to display the region name (this is as a tooltip), but probably through concantenation, also display the best selling salesperson in that region and the total sales for that person. I have all these currently as measures along the lines of this... Top Ranked Region = CALCULATE(SELECTEDVALUE(tblRep_List[Region]), TOPN(1,ALL(tblRep_List[Region]), [Amount Measure],DESC)) ...and have tried some concatenation to merge the measures togeher but obviously I'm then displaying the correct highest selling region , but not the correct salesperson within that region. Can anyone advise as to the best way to approach this? Thanks
Getting Top 5 base on DAX measure
I have two measures Rank PCM Simple = IF ( HASONEVALUE ( SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME] ), RANKX ( ALL ( SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME] ), [Avg_CSAT_Score] ) ) Top 3 PCM = IF( HASONEVALUE(SUPPORT__CW_OPS_CES_CSAT_PARTNER_CARE_TEMP[SURVEY_OWNER_FULL_NAME]), IF( [Rank PCM Simple] <= 3, [Avg_CSAT_Score], BLANK() ) ) Based on this I am getting the top 3 but i now need top 5 SURVEY_OWNER_FULL_NAME, however, no matter what I change in [Rank PCM Simple] <= 3 under the 2nd measure I am unable to get top 5. Idrissshatila amitchandak parry2kDAX to show top N item name based on row count
Hi, I have a fact table where each row is labeled by columns IssueType and IssueSeverity. I need a measure that prints the most common IssueType (based on rows labeled by it) and another measure that prints row count for that issue. Also I need similar measures for 2. common and 3. common IssueTypes and row counts. I managed to do that for a table visual. It tells me top 3 IssueTypes and counts. However, I want to use separate top three items on podium-like visuals or in Smart narrative visual and then using table is not an option. I have this measure for ranking (works for table visual): RankFaults = RANKX(ALL(IssueTypesTable), [CountRows]) CountRows measure used above is: CountRows = CALCULATE(COUNTROWS(MyFactTable), MyFactTable[IssueType]<>"") I have tried something like this to pick an N item but my tries doesn't work: Top2fault = CALCULATE(FIRSTNONBLANK(IssueTypesTable[IssueType], IssueTypesTable[IssueType]), [RankFaults]=2) How to resolve this? Thanks!
