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!
Create a Parameter off of a Column Query
I'm trying to create a parameter using a Query from the values in my dataset. From all of the tutorials I've watched, creating a Parameter requires you to have static values (i.e. a series of %'s, dates, metrics, etc.), however I'd like to have my parameter values be based off of the values in a column of my data (these names will change every time I refresh the data). Is this possible to do? For Example, I have a dataset with a column named "Baseline Scenario" with a series of scenario names listed below. However, when I upload a new dataset, or refresh the data, these Scenario Names may change and will no longer match what is listed, there may be more, less, etc. What I'd like to do is have the Parameter Table read from whatever is listed here so that every time I refresh the data, I can have a slicer automatically know the scenario names based off of what was included in the data. Is this possible?
