How to SUM by subgroups based on conditional criteria?

[Anonymous]

Hello,

I am struggling to find the answer to my issue in Power BI/DAX and am hoping that someone can point me in the right direction. Thank you in advance!

I have the following:

TABLE - 'Gifts' which contains:

3 COLUMNS - [Person_Id],  [Giftdate_FiscalYear],   [Revenue]

For example:
Person 001      FY2019        10.00
Person 001      FY2019        30.00
Person 001      FY2024        10.00
Person 002      FY2020        20.00
Person 002      FY2020        20.00
Person 002      FY2021        50.00
Person 002      FY2021        30.00
Person 003      FY2018        10.00
Person 003      FY2018        60.00
Person 003      FY2023      100.00
Person 003      FY2023        60.00

What is the best way to show the SUM of [Revenue] per [Person_Id] and per [Giftdate_FiscalYear] if all the following criteria is met?

* [Person_Id] has at least three entries
* [Giftdate_FiscalYear] is 2021 or greater
* [Revenue] is >= 60.00

Otherwise show a 0.00 for [Revenue] if any of the criteria is not met. Therefore the output would look like:

Person 001      FY2019          0.00
Person 001      FY2024          0.00
Person 002      FY2020          0.00
Person 002      FY2021        80.00
Person 003      FY2018          0.00
Person 003      FY2023      160.00

I have read up on & tested things like GROUPBY, SUMX, IF, CALCULATE, etc. for measures & tables but can't seem to come up with something that works. I think perhaps I need to break this down into multiple measures along with a calculated table to get the final proper output? My knowledge of DAX isn't that deep and so I am struggling to figure out what I need to look at or review via videos, etc.

Thank you again for any help or suggestions!

[parry2k]

@Anonymous my friend @TomMartens  has provided a great solution but here is my crack at it.

Count Rows = COUNTROWS ( 'Table' ) --count measure
Sum Rev = SUM ( 'Table'[Revenue] ) --sum revenue measure

Measure = 
VAR __table = 
ADDCOLUMNS (
    SUMMARIZE ( 
        'Table',
        'Table'[Person],
        'Table'[Fiscal Year], 
        "@Cnt", CALCULATE ( [Count Rows], ALLEXCEPT ( 'Table', 'Table'[Person] ) ),
        "@Cnt2021", CALCULATE ( [Count Rows], 'Table'[Fiscal Year] >= 2021 )
    ),
    "@Rev", [Sum Rev] 
)
RETURN
SUMX ( 
    __table,
    IF ( [@Cnt] >= 3 && [@Cnt2021] >= 1 && [@Rev] >= 60, [@Rev], 0 ) 
)

[TomMartens · Accepted solution]

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

[Anonymous]

@parry2k & @TomMartens,

Thank you both for taking the time to assist me with my scenario/question.  I truly appreciate it. 

As I am new to DAX and PowerBI, I will study how you both did this so I properly understand it.  I have marked this as solved and hope that both of your solutions might help someone else in the future that has a similiar issue.

All the best!

[TomMartens]

Hey @Anonymous ,

can you please add to each row of the expected result table which of the three roles is met like so

• row 1
  • rule 1 true
  • rule 2 true
  • rule 3 true
• row 2
  • ...

The reason for this, simply avoid misunderstandings like

• checking for 3 entries on or after 2021 or 
• checking for 3 entries also before 2021 but the revenue is only considered for years on or after 2021

Regards,

Tom

[Anonymous]

Hello Tom,

Thank you for your reply.  A suggestion that sounds like a good idea!  But how would I go about doing a proper grouping at the [Person Id] level while still checking each row to see it matches each criteria?  I am new to DAX/Power BI.  Is there a way to use a IF function for this properly?

[TomMartens]

Hey @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

[Anonymous]

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

[Anonymous]

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.00

The 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!