Why rankx function shows duplicated rank?
Hello, I am using this 1 dax in order to rankx my resource groups: Rank (Selected Category) = VAR cat = SELECTEDVALUE ( Fct_EA_AmortizedCosts_Daily[ResourceGroup] ) RETURN IF ( NOT ISBLANK ( cat ), VAR r = RANKX ( ALL(Fct_EA_AmortizedCosts_Daily[ResourceGroup]), -- ignore slicer on category [Total Amortized Cost], , DESC, Dense ) RETURN IF ( r <= 5, r, 6 ) ) and without any selection my ranked is duplicated: Why? If this is a matter of duplicated rows in fct amortized table? Best, Jacek
summarize rows based on a row specific measure like rankx
This is what I need: The sales amount % of items in the top 20 against the total sales. This must be dynamic, the top20 this week is different from the top20 last week. So I create a measure: [sales rank] = RANKX(ALLSELECTED('DIM-Item'),[Sales amt.]) this gets all items ranked based on the applied filters like week or item category. Now I want to calculate the sales amount of the items with rank < 21 SUMX( FILTER('DIM-Item', [sales rank] < 21), [Sales amt.]) So far so good. Only items in the top20 (rank < 21) get a value in the table. The problem is the total line. The total line show the full sales amount. This is because the [sales rank] in the total line = 1. It does not filter the item lines and then summarizes. It calculates the formula on the total line and then the filter for [sales rank] < 21 is not correct. Please check this table, especially the total line: Thanks for the help Kind regards, Johan
Rankx with Top N driving me nuts. Please help
Hi experts, I am having a hard time finding the correct rank with TopN and filters/slicers in matrix view. My data looks like this. These are my requirements. 1. Filter the data where Rank=Yes. 2. Create a Single select Parameter from source field excluding any blanks values. 3. Filter the data to respective source as per user selection. 4. Rank by Region and sales. 5. Add Rank, Region, Category, Include, Sales in Table/Matrix view 6. Add Include field to filters. Rank should be calculated after filtering the data. 7. Create a Top N type in parameter and show regions Rank less than Top N value. I tried multiple things by changing the context using All and All selected, Remove filters, Calculate but not able to filter the correct topn 1 ranks after the filter/slicer selection. Here is the pbix file with sample data PBIX link Please help. Many thanks in advance. Appreciate if someone invest some time to solve this. Regards, OmbirRankx function is behaving weird when used in table with extra columns and Slicers
Hi Experts. I am having a hard time understanding dax evaluation context in Rankx function. I want to rank the customers based on sales. These are the steps I have followed. Step 1: Creating an Measure Sales: SUM(Sales[Sales]) Step 2: Add Table to visual. Add Customer Name and Sales Measure. Sort Visual by Sales. Step 3: Create Customer Rank : Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),Sales[Sales_M],,DESC,Dense) Step 4: Drag Customer Rank to Table visual Step 5: Add visual level filter and include only Rank Source= Yes Step 6: Add a single select slicer based on Source and remove blank from slicer list. Select any one source. Step 7: Show the Rank by respecting both Visual level filter and Slicer. Upto Step 7 everything is working fine and rank is also correct. Step 8: Drag Category to Table and rank is incorrect now. I want to show Category as passive field without affecting the rank. Step 9: Changed Rank calculation and context to exclude Category from Filter context. Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy])),,DESC,Dense) Rank is correct now Step 10: Add Status to table. Now rank is incorrect again. Changed rank context again to remove Status from filter context Customer_Rank = RANKX(ALLSELECTED(Sales[Customer]),CALCULATE(Sales[Sales_M],REMOVEFILTERS(Sales[Categoy],Sales[Status])),,DESC,Dense) Rank is correct now Step 11: Add Status to slicer. Remove Unknow from Slicer values so that it doesn't appear in slicer select list and then filter on one status. Now data is filter for that status but Rank is not continuous like 1,2,3,5. It first calculated the rank and then filtered on status. I want rank to respect the Status filter when applied in slicer but status should act as passive field in the visual. Once Rank is correct then I need a Top N parameter where user can select Top N customers by respecting all slicers Here is pbix file. PBIX Thank you in advance for help. Regards, Cruncher
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
Stacked Bar chart: Group legend values
Hi folks, I have a stacked bar chart with products as a legend. There are a lot of products in my data. Therefore does not show all and gives this hint Personally i think this is crazy because the user will not even read this and see a wrong revenue: But ok, it is what it is. I tried to create a RANKX measure an show only the biggest legend values and group everything else as "Other" But Power BI will not let me put a measure in the legend... Any ideas?RANK Dax issues!
Hi all, I have the following dataset at my disposal and I'm looking at ranking the SumColumns shown in the dataset after a specific date of 2024, 9, 8 but as you can see there are duplicate values in the sum columns created (dax shown underneath) - and creating this rank measure would also mean dates before 2024, 8, 9 would need to be excluded. I included my current solution underneath these photos that duplicates the table, however I would want to avoid this and just write a new Dax with a new date in when the time comes to change ranges. Duplication solution: Thanks for your help in advance! JordanRankx should be easy! Why doesn't this work?
I am attempting to create the simplest of Rank measures! I have a single table (SalesData) of data, with a column called "Cost of Sales". I want to create a basic rankx function which ranks a dimension by the Cost of Sales. Sounds simple! But I cannot get this to show a result other than 1 every time no matter how I write this DAX. Any suggestions? I am sure it's something daft! COS Rank = //Attempt 1 //rankx(SalesTable, sum(SalesTable[Cost of sales])) //Attempt 2 //rankx(all(SalesTable), sum(SalesTable[Cost of sales])) //Attempt 3 //rankx(all(SalesTable), CALCULATE(sum(SalesTable[Cost of sales]))) //Attempt 4 //rankx(SalesTable, CALCULATE(sum(SalesTable[Cost of sales]))) //Attempt 5 //rankx(SalesTable, [COS Measure]) //Attempt 6 //rankx(all(SalesTable), [COS Measure]) //Attempt 7 //rankx(all(SalesTable), [COS Measure]) //Attempt 8 rankx(SalesTable, CALCULATE([COS Measure])) You can download a sample here. Just click on the download link at top right to download the PBIX. Thanks!
