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!
[Calculation Group] showing PY and CY in matrix when one option is selected on slicer
I have simple model fact and comparison (Dim). Due to performance issue fact was design with duplicated rows. each period has value for (1) CY and (2) PY. i created calucation group that is calculating value for CY and PY as: CY = CALCULATE( SELECTEDMEASURE(), REMOVEFILTERS(Comparison[ComparisonId]), Comparison[ComparisonId] = 1 ) It works but when i add it to matrix as below, when Comparison from dim is selected, then other value is hidden (in this case when CY is selected PY is hidden). Is there option to force CG to ignore comparison selection? So regardeles slicer selection always CY and PY will be visible? Switching of interaction is not an option due to page requirement and other measures presented in the matrix (CY, PY, delta, % change, calculation related to CY or PY based on selection) currently when slicer value is selected the other one goes blankIn Search of an Efficient Approach for Grouping and Analyzing Measures
Hey everyone! I will now present the scenario I have, and I need your help to see how you would approach it. I have “solved” it in an incorrect way because my solution consumes too many resources and is inefficient I have 5 measures, measure1, measure2 … measure5. These measures visually need to be grouped (as a visual separation without totaling) into two groups: Group 1: measure1 …3 Group 2: measure4, measure5 For each measure, I have: Budget, Real, Budget LY, Real LY, Budget YTD, Real YTD, Budget LYTD, Real LYTD. I have 2 companies: Company 1, Company 2. The measures were developed before the existence of calculation groups (but I don’t know if the solution involves calculation groups). Now I will show you the grid structure I am looking to visualize. I need your help on how you would approach the problem. Thanks in advance.
Grouping Question
I'm trying to write a dax measure that will group Sales by Product. I can see via a table with Sales and Product columns what the result should be... Yet the following measure is off somehow... Gross Sales by Product = CALCULATE( SUM(financials[Gross Sales]), ALLEXCEPT(financials,financials[Product]) ) What am I doing wrong? EDIT: There was somehow a country filter applied to the first table, it's calculating the same as my dax measure now. So just to confirm that application of DAX code works to group by...?
Calculation group using filter modifier looks like a bug
Hi, guys, I have found a very strange calculation Percentage between the measure and Calculation Group. I expected to see the same calculations using filter modifiers in Calculation Group. I could not find any answer in your book about this feature in calculation group. Can you help me?Is it a Bug? And if I add the measure Sales% under the action of the calculation group, then the calculations become the same. How one measure can influence to another measure?It is magic. The dataset is here:https://fex.net/s/9rpnlb0Calculation group that affects calculation item
Hello dear members I have created a calculation group that actually reference measures. So I have a calculation item for actual sales =[sales] A calculation item for PY sales =[PY sales] A calculation item for YOY% =[YOY], A calculation item for Target sales =[target] And calculation item for Target dif=[target dif] I have added all of them in to a matrix. Now I want to add a button and change between 2 different targets. Target 1 and target 2. I have created a separated table with target 1 and Target 2 and I have used an if statement to change the measure based on the selection. The target measure is something like that If( selectedvalue(target[target])="target 1", [target1], [target2]) It works fine but I was wondering if there is a more efficient way to resolve this and if it is possible to resolve it again by using calculation group. Thank you in advance 🙂
Calculate a date sequence on a row level per ID (in DAX)
Hi there, I have this data set where each patient goes through different treatments (each row is a new treatment). I would like to add a new column (using DAX) that creates a sequence of numbers from 1 to "N" for each Patient, being 1 assigned to the earliest date. This is what the new column should look like: Patient Created on (d/mm/yyyy) Treatment number (calculated column) Patient A 16/08/2023 3 Patient A 5/05/2023 2 Patient A 25/03/2023 1 Patient B 13/08/2023 2 Patient B 5/05/2023 1 Patient C 19/06/2023 1 Patient D 8/09/2023 2 Patient D 14/07/2023 1 Thanks!
