how to group rows with calculated columns

[luis_pflucker]

Hi,

I have a table with calculated columns  as you see in the attached picture, I am trying to group in same row for example Defect ID 32013, 32026, 32053, etc where as a result the column #TCs Blocked will be the sum of quantities in such rows.

#TCs Blocked is the sum of calculated column: 

ATRKBlockedCOL = calculate(countrows (Execution_Report), calculatetable ( Execution_Report, Execution_Report[TEST CASE STATUS] = "Blocked")) + calculate(countrows (Execution_Report), calculatetable ( Execution_Report, Execution_Report[TEST CASE STATUS] = "No run"))

Please, any suggest?

Luis

[MPMP]

Can share your DAX maesure for RANKX and comparision ? let me take a look at it and see where you are going wrong

[luis_pflucker]

I tried with:

Column = RANKx(all('All Defects (2)'[Defect number]),'All Defects (2)'[ATRKBlockedCOL])

Where Defect number = Defect ID and ATRKBlockedCOL is #TCs Blocked

[MPMP]

Try using the below dax measure:


RANKX(

FILTER(

'Table Name',

'Table Name'[Column Name1] = EARLIER('Table Name'[Column Name1]) 

),

'Table Name'[Column Name2],,DESC,Dense

)

The above measure will group your records and assigns rank inside the group

[luis_pflucker]

Hi, you mean: 

Column = RANKx(filter('All Defects (2)','All Defects (2)'[Defect number]= earlier ('All Defects (2)'[Defect number])),'All Defects (2)'[ATRKBlockedCOL],,DESC,dense).

See attached what I got.

[MPMP]

You need to create another column and get the MaxRank in each group, if the max rank = 0 then 'N' if the max rank =1 then 'Y'

[MPMP]

Hi Luis,

I hope my solution helped you to achieve what you were looking for, could you please accept it as solution ?

[luis_pflucker]

Hi, I am still trying to figure out gow to apply your suggestion.

[MPMP]

group the records and Rank Defect ID by TCs Blocked, get the max rank for each Defect ID in a new column, now if the rank is > 2 that will be the duplicate Defect ID make it as 'Y', if its not greater than 2 then thats not duplicate.
some thing like this

[luis_pflucker]

Hi MPMP,

I tried with RANK.EQ and also using RANKX, and got some results, but not sure how you did suggest the entire DAX, please could you share details?.Thanks.

[selimovd]

Hey @luis_pflucker ,

usually the values will be summarized automatically. 

The just won't summarize by ID when you choose for one of the measures the "Don't summarize" option:

Make sure all measures in this tables are set to an aggregation like SUM and then the values should summarize.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[luis_pflucker]

Hi Denis,

yes, what you suggested is already done, Defect ID as not summarize, and #TC Blocked and BUATotro as sum. My issue is I am trying to group those duplicated Defect ID, where values in column #TC Blocked are sum, and create a row where shows something like Y or N, for example : 32013 with 110 #TC Blocked should be in a row as Y, 32026 with 142 # TC Blocked in a row with Y, and so on, only 31990 with 13 #TC Blcked in a row with N. I am trying to group using any algorithm based on columns BUATotro or BUATotroYN. See attached picture on what I want to do.

[PaulDBrown]

If you need a new column in the visual which returns "Yes" for values greater than 0 else "No", then create a measure along the lines of:

Yes or no = IF(SUM( Table [BUATotro]) >0, "Yes", "No")
or
If BUATotro is a measure,

Yes or no = IF([BUATotro measure] >0, "Yes", "No")

[luis_pflucker]

Thanks Paul, but still had the issue because I wanted to group even Yes or Not, where each Defect ID is suming #TCs Blocked in only one row.

What I did, is based on your suggestion, I calculated a column Yes or No, and on this new column I choose not count, not summarize, but Last (as attached). Thanks!!!