Forum Discussion

chadg's avatar
chadg
Regular Visitor
2 years ago
Solved

Grouping records in table visual

I have a table visual in a report with a list of work items that are sorted by Job# and User Name. I am trying to create a lotting system for quality control that groups each set of work items into g...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    I have used a combination of Power Query and a DAC calculated column formula to solve the problem (though this can be solved in Power Query only as well).

    PBI file attached.

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, chadg 

Based on your information, I create a sample table:

 

Then create calculated columns:

Calculation = 1
Calculation 1 = 
SUMX(
    FILTER(
        'Table',
        'Table'[Index] <= EARLIER('Table'[Index])
    ),
    'Table'[Calculation]
)

Weld Lot = 
VAR _Rank = RANKX(
    FILTER(
        'Table',
        'Table'[User Name] = EARLIER('Table'[User Name])
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)
RETURN
    IF(
        ISBLANK(_Rank) || MOD(_Rank, 10) = 0,
        10,
       MOD(_Rank,10)
    
)

 

Here is my preview:

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

chadg's avatar
chadg
Regular Visitor
2 years ago

Anonymous Thank you for your help.

 

In your second picture, index 5 and 6 are given lot number 3 and 4. They should be 1 and 2 because the user name is the same, but the job number changed. Can you help with that?