Dynamic ranking of a parameter value
Hi! Would appreciate help figuring out how to create a measure that calculates the dynamic ranking of a parameter value. Let me provide context. I have a 'Transactions' table with columns <ID>, <Weeknum>, <Amount>, <Category>. I'm interested in looking at the aggregate amount per week per ID, and its ranking. My report will have a slicer filter where the user can select different categories, therefore my grouped table becomes dynamic. Additionally, the user is able to input a value as a parameter that represents a weekly aggregate amount. Let's call that parameter value the 'threshold'. The objective is to figure out what rank is this threshold out of the grouped table. Example of original 'Transactions' table ID Weeknum Amount Category 111 30 100 A 111 30 50 B 111 31 200 B 222 31 150 A 222 31 200 A 222 32 300 A Example of Grouped Table and ranking if filtered by Category A ID Weeknum Weekly Amount Ranking ASC 111 30 100 1 222 31 350 3 222 32 300 2 So if the threshold selected = 300, then my ranking measure should return 2. Was thinking of using RANK.EQ function with threshold as the 'number' argument, but I'm stuck at the 'ref' argument. I can't pass a summarized table as the ref argument as it gives me the error that the base table can't be found. Please let me know how can I modify this to achieve the desired result.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!Calculate Change in percentage based on multiple Quarter Selection
Hi All , I am struggling to find the soultion for the below problem statement. I have to create a measure to calculate the %change from selected quarter range. For example, we have data of 2023 from each month with salesDate. Input Data: Month-Year Quarter-Year SalesAmount 01-2023 Q1-2023 10 02-2023 Q1-2023 20 03-2023 Q1-2023 30 04-2023 Q2-2023 40 05-2023 Q2-2023 50 06-2023 Q2-2023 60 07-2023 Q3-2023 70 08-2023 Q3-2023 80 09-2023 Q3-2023 90 10-2023 Q4-2024 100 11-2023 Q4-2024 110 12-2023 Q4-2024 120 So when Q1-2023, Q3-2023 & Q4-2023 are selected from slicer, desired result should be i.e. Quarter-Year SalesAmount % Change Q1-2023 60 0.0% Q3-2023 240 300% Q4-2023 330 37.5% but if only Q1-2023 & Q4-2023 are selected then the desired output should be Quarter-Year SalesAmout % Change Q1-2023 60 0.0% Q4-2024 330 450% @Greg @HotChilli @bhanu @bhanu_gautam @lbendlin @Irwan Many thanks
Top N and others in calculation group
Hi community I have followed this great material by SQLBI to do a dynamic TopN & Others solution : https://www.sqlbi.com/tv/implementing-the-top-n-and-others-pattern-using-calculation-groups-in-dax-unplugged-28/ I have a issue with incorrect Ranking. I can't figure out why.. Any ideas what to do? In some filter cases it return correct ranking, but mostly incorrect. Relevant measures below: Ranking = IF ( ISINSCOPE ( 'Customer Names'[Customer_Name] ), VAR CustomersToRank = [TopN Value] VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] ) VAR Val = [ValueForRanking] VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others" RETURN IF ( IsOtherSelected, -- Rank for Others CustomersToRank + 1, -- Rank for regular Customers IF ( Val > 0, VAR VisibleCustomers = CALCULATETABLE ( VALUES ( 'pbi DimCustomer' ), ALLSELECTED ( 'Customer Names' ) ) VAR Ranking = RANKX ( VisibleCustomers, [ValueForRanking], Val ) RETURN IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking ) ) ) ) ValueForRanking = VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] ) VAR Val = SWITCH ( TRUE, MeasureForRanking = "Sales", [Sales], MeasureForRanking = "Order intake", [Order intake], MeasureForRanking = "Order bank", [Order bank], [Sales] ) RETURN Val Calculation item: IF ( NOT ISSELECTEDMEASURE ( [Ranking] ), VAR ValueOfAll = CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Customer Names' ) ) RETURN IF ( NOT ISINSCOPE ( 'Customer Names'[Customer_Name] ), -- Calculation for a group of products ValueOfAll, -- Calculation for one product name VAR CustomersToRank = [TopN Value] VAR ValueOfCurrentCustomer = SELECTEDMEASURE () VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others" RETURN IF ( NOT IsOtherSelected, -- Calculation for a regular product VAR Ranking = [Ranking] VAR Result = IF ( NOT ISBLANK ( Ranking ), ( Ranking <= CustomersToRank ) - ( Ranking = CustomersToRank + 1 ) ) RETURN IF ( Result, ValueOfCurrentCustomer ), -- Calculation for Others VAR VisibleCustomers = CALCULATETABLE ( VALUES ( 'pbi DimCustomer' ), ALLSELECTED ( 'Customer Names'[Customer_Name] ) ) VAR CustomersWithValues = ADDCOLUMNS ( VisibleCustomers, "@ValForRanking", [ValueForRanking], "@SelMeasureAmount", SELECTEDMEASURE () ) VAR ValueOfTopCustomers = SUMX ( TOPN ( CustomersToRank, CustomersWithValues, [@ValForRanking] ), [@SelMeasureAmount] ) VAR ValueOfOthers = ValueOfAll - ValueOfTopCustomers RETURN ValueOfOthers ) ), SELECTEDMEASURE () ) Would appreicate any ideas/help to fix this Thanks
Rank highest to lowest value based on another measure
I'm not sure if this is possible but I need some help creating a measure(s) (due to access restriction I can only add in measures). I have 2 existing measures - Total SI and Rolling 30D Events: Total SI = CALCULATE(COUNTA(Event[EventSK]),Event[Is Significant Incident Flag]=1) Rolling 30D Events = CALCULATE(DISTINCTCOUNT('Event'[EventSK]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -30,DAY),ALL('Date')) For each day where an SI is not blank, I need to get the equivalent Rolling 30D Event value then rank that from highest to lowest similar to the table below: Once I have the ranking, out of my total SIs in the last 12 months, I need to get the Rolling 30D Event value at 75%. In the table below, how can I get the value of 118? Appreciate all the help I can get
RANKX and muliple slicers
Hi, I have a set of stores with revenue per week/year. Each store has a ID, belongs to a revenue group and price class. I want to rank the stores by the revenue growth compared to a time period previous year. I have created a measure that calculates the %-revenue growth as follows (and it's working), but have some issues with the ranking. I have tried using this measure without any luck: Rank rev_growth_ALL = RANKX( FILTER(ALL(Store_ID), dStores[Revenue Group]=SELECTEDVALUE(dStores[Revenue Group])&& dStores[Price class]=SELECTEDVALUE(dStores[Price class])), [revenue_growth])) The issue is that I have three slicers for the Store ID, Revenue group and Price class respectivly. If I filter on a specific store without selecting any of the other slicers, I want to return the rank of the selected store relative to all stores within the same revenue group and price class. This is manged by my above DAX code. HOWEVER, if I select a revenue group without selecting any stores, my stores are ranked per price class, while I want it to show the rank for all stores within that revenue group disregarding the price classes. Thus, it's the different combinations of my slicers which creates some issues with the ranking. Any idea on how to solve this?
RANX in ascending order
Hi All, In my table I have companyName and featureName columns. I want to count rows in the table and rank based on featureName. This worked for me when I did ranking in descending order. Using the following code: FeatureRank = RANKX( ALL('Features'[FeatureName]), CALCULATE(COUNTROWS('Features')), , DESC ) However, If I do this in an ascending order, the output is not quiet what I expect in one scenario. FeatureRank = RANKX( ALL('Features'[FeatureName]), CALCULATE(COUNTROWS('Features')), , ASC ) For one of the companies I had all the features used (there are 11 features in total) so, it return the rank correctly in ascending order based on featureName. The ranks were changing from 1,2,3,...11 based on the number of times the featureNames occured. However for another company I had only 3 featureNames. The rank that I received for these 3 features were 9,10 and 11. How do I get ranks 1,2,3 for the second company? Note that I could have another company with different number of features used. How can I consistently rank the least used feature as 1 for each company?
