Forum Discussion

zelda88's avatar
zelda88
Regular Visitor
8 years ago
Solved

Help?

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!

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] ) )

6 Replies

  • BKirsch12's avatar
    BKirsch12
    Resolver II
    8 years ago

    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.

     

     

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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] ) )
      • BKirsch12's avatar
        BKirsch12
        Resolver II
        8 years ago

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

    • zelda88's avatar
      zelda88
      Regular Visitor
      8 years ago

      BKirsch12

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

  • zelda88's avatar
    zelda88
    Regular Visitor
    8 years ago

    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!