Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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] ) )
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!
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.
Yes, I tried this and it worked! 🙂 Thank you for taking the time to help me out!
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] ) )
I tried this and got the same results. Nice Solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |