Forum Discussion
How to SUM by subgroups based on conditional criteria?
Hey Anonymous ,
your last explanation is exactly what is needed to avoid misunderstandings.
First I created a calculated column to extract the year to have a numeric representation of the FY, this makes checking rule 2 more simple:Giftdata_FiscalYear_Value = RIGHT( 'Gifts'[Giftdate_FiscalYear] , 4)It is recommended creating this column using Power Query, or even better already in the source system. Nevertheless, I use DAX because because simplicity.
Then I use DAX to create this measure:Measure = var thevalue = SUMX( FILTER( ADDCOLUMNS( FILTER( ADDCOLUMNS( SUMMARIZE( 'Gifts' , Gifts[Person_Id] , Gifts[Giftdata_FiscalYear_Value] ) , "# of entries" , CALCULATE( COUNTROWS( 'Gifts' ), ALL( Gifts[Giftdata_FiscalYear_Value] , Gifts[Giftdate_FiscalYear] ) ) ) , [# of entries] >= 3 && [Giftdata_FiscalYear_Value] >= 2021 ) , "sum of rev" , CALCULATE( SUM(Gifts[Revenue] ) ) ) , [sum of rev] >= 60 ) , [sum of rev] ) return IF( ISBLANK( thevalue ) , 0 , thevalue )This allows to create this table visual:
Please be aware that showing 0 instead of BLANK (meaning an empty cell) can become costly the larger the table gets.
Hopefully, this provides what you are looking for.
Regards,
Tom
Super UserHey Anonymous ,
sorry, my bad! When I ask for the rules for each row of the expected result table, this is not part of the measure, but instead part of the business requirement. I consider your initial description not 100% unambigous. Explaining how the values you are looking for are calculated row by row helps us to define a measure.
Regards,
Tom
Tom,
The rows are already defined in the table with data similar to the example. Basically each person in the table has their own ID, a donation gift amount, and the fiscal year that the gift occurred within.
What I am unsure of is how to look at the table in terms of grouping the person Ids together as groups and within that, to check for any donations that occurred in 2021 or later and then summing all donations that are 60.00 or but totalling them within each financial year. Basically it would be main group of person Ids -> subgroups of fiscal years for each person -> and then looking at all amounts within the fiscal year subgroups still by person. When all criteria are met (person has to have more three or more entries existing first, then those entries for fiscal years 2021 and beyond, and then checking to see that the sum of all donations within the fiscal year are 60.00 or more) then the donations within each fiscal year per person are either summed or it is to show a 0.00 amount.
I hope that helps. Sorry if I am confusing! I appreciate your assistance.
I hope that
- TomMartens
Hey Anonymous ,
no, this does not help (sometimes I'm slow), my question: Does person001 pass the 1st rule, more than 3 entires, but for FY21 the sum of donations does not exceed exceed 60 hence the result is 0.
Regards,Tom
Hi Tom,
No, I am sure I am not explaining it well. Sorry!
Yes, the first rule would be passed for person 001 as this person has three entries (if rule 1 had already failed at this point, then person 1 would just be skipped for the output or all rows for that person could show the person id, fiscal year, and just 0.00 for all donations.
Then rule 2 is checked for the same person. Are there any rows for this person that are year 2021 or later? If not, person 001 then should be marked as having a 0.00 revenue. But in the example, there is at least one row of year 2021, so that rule is passed. Therefore it notes which years are 2021 and later and moves on to check rule #3.
For rule three, for all years that 2021 or later, are there rows that contain donations that are 60.00 or more? If not, then the revenue for person 001 is 0.00. In this case of the example, person 001 has one year of 2024 but the donation in that row is not 60.00 or more.
Therefore the final result for person 001 should be
Person 001 FY2019 0.00
Person 001 FY2024 0.00The goal is to check the table first starting by looking at each person Id as a it's own group. Within that group for the first person, it should check if there are years after 2021. If so, check the donation amounts for the qualified rows and sum them together within each of those years. Otherwise the person should just show all years after 2021 as 0.00 for revenue.
Thank you again for your help!