Help?

[zelda88]

I have provided an example of what I'm hoping to do. Please do not hesitate to ask for clarification if the example is not clear. 

Name       Dependent       Form No.       Paid

Emil R.      Emily                 134                10 

Emil R.      Emily                 267                20

Emil R.      Emily                 372                5

Jon S.        Robert              573                19

Jon S.        Robert              213                38

Eve W.       Camilla            196                 63

Eve W.       Eve W.             196                 63

RESULT: 

Name       Dependent       Paid

Emil R.      Emily                 25  

Jon S.        Robert              19  

So, what I'm hoping to do is as follows:

If the number of forms per Dependent exceeds 1, I would like to obtain the total of "Paid" for all subsequent forms (not including the first) per dependent. We can find which forms come first in the series by inspecting the "Form No." wherein a larger form number indicates that a form comes later in the series. 

How do we automate this calculation? Any ideas would be very much appreciated!

[zelda88]

I have provided an example of what I'm hoping to do. Please do not hesitate to ask for clarification if the example is not clear. 

Name       Dependent       Form No.       Paid

Emil R.      Emily                 134                10 

Emil R.      Emily                 267                20

Emil R.      Emily                 372                5

Jon S.        Robert              573                19

Jon S.        Robert              213                38

Eve W.       Camilla            196                 63

Eve W.       Eve W.             196                 63

RESULT: 

Name       Dependent       Paid

Emil R.      Emily                 25 

Jon S.        Robert              19  

So, what I'm hoping to do is as follows:

If the number of forms per Dependent exceeds 1, I would like to obtain the total of "Paid" for all subsequent forms (not including the first) per dependent. We can find which forms come first in the series by inspecting the "Form No." wherein a larger form number indicates that a form comes later in the series. 

How do we automate this calculation? Any ideas would be very much appreciated!

[BKirsch12]

This requires a few steps

1. Order by Form No. Ascending

2. Serialize the unique combination of Name and Dependent.

3. Filter out the first occurence on the index

4. Then do a group by on name and dependent while summing the paid column.

Example file has all the steps:

https://drive.google.com/file/d/152XDCLRmkex2xLqweagkwY-EXzHBi6E2/view?usp=sharing

This video shows how to do the serialization:

https://www.youtube.com/watch?time_continue=36&v=-3KFZaYImEY

Let me know if that helps.

[zelda88]

@BKirsch12

Yes, I tried this and it worked! 🙂 Thank you for taking the time to help me out!

[Zubair_Muhammad · Accepted solution]

@zelda88

This DAX calculated table might work as well

Sample file attached as well

From the Modelling tab>>New Table

New Table =
VAR Filter_Dependents =
    CALCULATETABLE (
        Grouped,
        FILTER (
            SUMMARIZE (
                Grouped,
                Grouped[Name],
                [Dependent],
                "Count", DISTINCTCOUNT ( Grouped[Form No.] )
            ),
            [Count] > 1
        )
    )
VAR RANK_Forms =
    FILTER (
        ADDCOLUMNS (
            Filter_Dependents,
            "RANK", RANKX (
                FILTER ( Filter_Dependents, [Name] = EARLIER ( [Name] ) ),
                [Form No.],
                ,
                ASC,
                DENSE
            )
        ),
        [RANK] > 1
    )
RETURN
    SUMMARIZE ( RANK_Forms, [Name], [Dependent], "Paid", SUM ( Grouped[Paid] ) )

[zelda88]

@Zubair_Muhammad

This is a very elegant solution! Thank you for your help! 🙂

[BKirsch12]

I tried this and got the same results. Nice Solution.