Count and Aggregate by multiple values
Hello! So, I'm trying to resolve this, I believe the answer is righ there in front of me, but I just can't assemble my thoughts right... I have the table below and I need 2 measures: 1) Quantity of distinct projects by department and 2) For each Project and department, how many tasks is a owner responsible by? I tried to use "group by", but I did it in two steps: firts, I created a new calculated table, grouping by Project, Task, Department and Owner. Then, I created a measure using "group by" like this: COUNTROWS(GROUPBY("groupstep1","groupstep1[Owner]","groupstep1[Task]","groupstep1[Project]". It actually worked, but I was wondering if there is any way to do this without the firts step. Or any better way....
SUMX SUMMARIZE group by company in a table
Hi all. I have a simple table made of 2 fields and 1 measure: where _Sales Denominator YA ORG = CALCULATE( [_Sales YA ORG] , ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]) , ALLSELECTED(Period[Period]) ,ALLSELECTED('Market Breakdown'[Country]) ,ALLSELECTED('Organic Claim'[Organic Claim]) , VALUES('W Segment'[W Segment]) , VALUES('Market Brkdown'[Hier Level Name]) , VALUES('Ad Hoc Analysis View'[Category]) , VALUES('Ad Hoc Analysis View'[Segment]) ) what I want to do is to delete the country field and obtain one row for each company with the sum of each _Sales denominator YA ORG for each company. For example I want this result: Company _Sales denominator YA ORG PRIVATE LABEL 3402952 STRAUSS GROUP 206 TCHIBO 36854 TONYS COFFEE ROASTERS 2895 VERGNANO 1131 WERTFORM 3987 WICKED JOE LLC 23592 WOSEBA 1035 So what I've tried is to modify the measure this way: _Sales denominator YA ORG= SUMX( SUMMARIZE( 'Ad Hoc Analysis View', 'Ad Hoc Analysis View'[Company], "x_per_company", CALCULATE( [_Sales YA ORG], ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]), ALLSELECTED(Period[Period]), ALLSELECTED('Market Brkdown'[Country]), ALLSELECTED('Organic Claim'[Organic Claim]), VALUES('W Segment'[W Segment]), VALUES('Market Brkdown'[Hier Level Name]), VALUES('Ad Hoc Analysis View'[Category]), VALUES('Ad Hoc Analysis View'[Segment]) ) ), [x_per_company] ) But I obtain this table, with summarized values that are different from what I expect: Do you have any suggestion? ThanksDAX Summarizecolumns and then countrows per group
Hi, I have query with 3 dimensions (Continent/CountryRegion/City) part of paginated report. I would like to highlight only those child groups where there is 1 item. So I need to get a count of items per each parent group. In this case, let's write the query, to be more exact. EVALUATE VAR __t1 = SUMMARIZECOLUMNS ( Customer[Continent], Customer[CountryRegion], Customer[City], "Amount", [Sales Amount] ) RETURN GROUPBY( __t1, Customer[Continent], Customer[CountryRegion], "Count" , countX(CURRENTGROUP(), Customer[CountryRegion] ) ) ORDER BY Customer[Continent] DESC Which brings me this Result I am looking for is And in a case where I have 3 dimensions EVALUATE VAR __t1 = SUMMARIZECOLUMNS ( Customer[Continent], Customer[CountryRegion], Customer[City], "Amount", [Sales Amount] ) RETURN GROUPBY( __t1, Customer[Continent], Customer[CountryRegion], Customer[City], "Count" , countX(CURRENTGROUP(), Customer[City] ) ) ORDER BY Customer[Continent] DESC I get But I would like to see How should I approach this? Thank you!
Index Column Groupby() and currentgroup()
Hi all, i have a table with the columns ID and Category. The goal is to create a new table that lists the IDs, and an index of the number of categorys an ID had in its lifetime. Example: Data Table ID / Category 00001 / SWF Info 00001 / Response FOMA 00001 / Creditreform 00001 / Legal IKU 00001 / Bankcrupcy Desiered Result should be: ID / Index 00001 / 5 I tried thisCreate a new Table with only the last Case steps from a existing Table
Hi, i´ve been trying to create a new Table using the Summarize(), Addcolums() and Lastnonblank() function. I have a table called "Bearbeitung" which lists all cases with their single steps, this table is sorted in Querry by date (increasing). I want to create a new Table called "letzte Bearbeitung" which lists for every distinct case "Vorgangs_ID" the the last step based on the last time it was edited. I ran into an error. My code doesnt work right. The LASTNONBLANK() funktion wont give the last result for ID_Nonth_UserID and Status_after_Handling. Don't know why, what am i doing wrong here? Is there maybe a better solution using the GROUPBY() function?Get latest date for certain task ID
Hi, I have the data as shown below: Entity Id Covenant ID Covenant Sub Type TaskID Latest Comment Date 21632312 108 Net Interest Cover 38499 11/11/2020 21632312 108 Net Interest Cover 38499 02/11/2020 21632312 109 Leverage Ratio 38508 11/11/2020 21632312 109 Leverage Ratio 38508 02/11/2020 21632312 110 Capital Expenditure 38517 02/11/2020 21632312 110 Capital Expenditure 38517 11/11/2020 for each covenant ID and task ID I only want to show the last 'Latest Comment Date'. For instance if you look at Task ID 38499 I only want to see the 'Latest Comment Date' as 02/11/2020 What measure can I write for this? Kind ReDAX measure to calculate the sum of transactions for all users equal or above a threshold percentile
Hi, I have what I thought was a fairly easy DAX problem, unfortunately I am struggling with it. I have a user input for a parameter, which takes the input of a percentile. In my data, I have client_IDs associated to any and all of my transactions. I would like to get a measure that calculates the total number of all transactions for clients with a number of transactions at or above the threshold percentile specified by the user. For example, if a user inputs 75 in the what-if parameter, the measure should produce the total number of transactions for all clients equal to or above the 25th percentile as determined by their transactions count. One caveat is that, I need to handle scenarios where there are ties. I want to settle ties arbitrarily. So for example let's say to be in the top 80 percent of clients you need to have at least 2 transactions and to be in the top 70 percent the client needs at least 3 transactions then to get the top 75 we would have to randomly choose to add some of the clients with 2 transactions to our total and to exclude the others. The ones which are kept for the total vs the ones that are not, is not important. If it were in python it would look like this: number_of_clients = dataset.groupby('client_id').size().shape[0] top_count = dataset.groupby('client_id').size().sort_values(ascending = False).cumsum()[:math.ceil(what_if_parameter*number_of_clients)].sum()
