RANKX
Hi , I am trying to create a RANK for product category based amongst all prodcut categories and display it on one KPI CARD( so it would just show the RANK, based on Total amount of sales desc. The Rank for specific Category will be displayed based on the filtered retailer. so for example. If you select Walmart, the specific category named = " Clothes" ranks 5th or 6th or 7th amongst the overall sales of all catgories. you can either specify the category name explicitally in the measure, or have a slicer/ filter on the CARD visual. Either way is fine. I wrote the measure and It works absolutely fine when I add a table that contains Retailer, Product Category and Rank. But IF I only soon as I add a Card visual to only show the rank number, and slice it by the Category Name= "Shirt", It will throw off the number by 1 rank at least. Does anyone have any idea on why that might be ? Here is my 2 DAX measures: Total_Sales= VAR VAL = CALCULATE(SUM(Sales[Sale_Amount]),FILTER(ProductCategory, ProductCategory [Name<>"MISC"),FILTER(ProductCategory, ProductCategory [Name] <>BLANK()), FILTER('Calendar','Calendar'[Year]=[Max Year])) return VAL Rank_Sales VAR Val = RANKX ( FILTER(FILTER(ProductCategory, ProductCategory [Name]<>"MISC"),FILTER(ProductCategory, ProductCategory [Name] <>BLANK()) , [Total_Sales],,DESC,Dense) RETURN VAL RANKx RANKX measure for each group
Rankx dax function in column with complicated measure
am having an issue with using the rankx function in a column in my table. I can calculate the rank with a measure, but I need to use the rank values as the x-axis on a bar chart. So, I guess I need to calculate the rank value in a column in my table. My other issue is that the value "expression" used for rankx is a measure consisting of a bunch of other measures, calculated from different rows in my main table. The measure used is a forecast that that looking at the year-to-date cost from last year compared to the hole last year cost. This percentage value is then used to calculate a forecast of this year cost, using the year-to-date cost for this year. This forecast measure is then compared to the total budget of this year, to see if the different counties are expected to keep their budget. This forecast vs budget measure is what I want so rank between the different counties. As the measure is complicated, I often get an error saying that there is a circular reference. Or I just get a bunch of 1's. Here is a report where I have illustrated the issue. https://1drv.ms/u/s!Akk3xPcd4O64gaop19baJ5m1yd6ENA?e=soxb3l
RankX Dense
I am trying to rank sales people based on YTD revenue total. When I rank the brokers based on the broker name column the rank repeats/starts over multiple times randomly. If I rank everything based on the entire table the rank is a little more consistent however there are ties in the order despite using the Dense tie breaker. Rank = RANKX(ALL('Broker Totals'[Broker Name]), [running total],,,Dense) - causes the ranking to be inconsistent, multiple people be ranked #1 regardless of the sales volume the rank starts over multiple time within the first 25 sales reps. see snip Rank = RANKX(ALL('Broker Totals'), [running total],,,Dense) - gets me closer to my desired result however despite using the dense commmand in the DAX it doubles up the rank order despite their revenue totals not actually being tied. see snipTop and Bottom 5 with RANKX based on other measures
Hi all, I need to calculate a top and bottom 5 employees based on the result of two measures. I tried to use RANKX but the problem is that I need to take into account two measures. Like a double ranking... Below 3 examples: 'Collis checked' and 'Total Over Picking' are measures 1) 2) 3) 1) Show the BOTTOM 5 'GOLD numbers bereider' with the most 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 294 2. 311 3. 450 4. 844 5. blank 2) Show the TOP 5 'GOLD numbers bereider' with the less 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 065 2. 431 3. 942 4. 248 5. 816 3) Show the TOP 5 'GOLD numbers bereider' with the less 'total over picking'. (!) Take into consideration who has the most 'collis checked'. (!) Total over picking can be BLANK or contain a number In the example left this is the desired result: 1. 696 2. 487 3. 140 4. 051 5. 722 I created a test powerbi file with sample data. You can download the test file via this link in WeTransfer: https://we.tl/t-tr6d0dzIQH Hope someone can help out. I already broke my head hours on this one... 😞
Rankx Value by category hierarchy for each dynamic time period in matrix
Im trying to get this but i get this here is my current code RankbyDate = VAR Region = ISFILTERED(Station_Departure[xRegion_t]) VAR SubRegion = ISFILTERED(Station_Departure[xSubRegion_t]) VAR Station = ISFILTERED(Station_Departure[xStn_t]) VAR Result = SWITCH ( true(), Region&&NOT(SubRegion)&&NOT(Station),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xRegion_t]), [TotalAgreementLevelScore],,DESC)), SubRegion&&NOT(Station)&&NOT(Region),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xSubRegion_t]), [TotalAgreementLevelScore],,DESC)), Station&&NOT(Region)&&NOT(SubRegion),CALCULATE (RANKX(ALLSELECTED(Station_Departure[xStn_t]), [TotalAgreementLevelScore],,DESC))) RETURN Result
Question about Rankx in Matrix table format
Hello i am trying to use Rankx and matrix table to setup a format in BPI like this Rank Territory number BF3.8 BF5.0 BF 5.8 Total 1 1111 10 5 5 20 2 1112 5 5 5 15 The closest format i can think of is to use Matrix table. The problem is that Rankx can only be placed as VALUES...so here is what i have now below. Question 1) any way to move the ranking to the row? so before the territory number column? Question 2) how to get rid of ranking for each sub product category. i only want to know $$, dont really care about how they rank individually. i do want to keep the overall ranking though thank you all!!
Dynamic Ranking of Product in Hierarchy
Hello all, this is the first I am completely stuck with a problem in DAX. My problem is supposedly simple, but I believe the RANKX function is very hard to apply in real-world problems. Desired end-result I simply want to rank 3 products in a geographical hierarchy, considering also the slicer context of the report page (geography and a date range). Both rows and columns are based on fields. Ideally, the table has just one measure. The ranking shall be based on the latest date selected on the sheet. This is the fact table This is the geography table The date table is just a standard calender connected via the date field. I understand from reading countless other posts on RANKX issues, that in the end I could generate mutiple variables in a measure and then check with ISFILTERED() the current hierarchy level. I guess the issue that I have, is how to calculate the ranking on an aggregated level, like here Subregion? Can someone provide a solution pattern for this? Many thanks in advance!
