Forum Discussion
How to SUM by subgroups based on conditional criteria?
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!
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
9 Replies
- TomMartens
Super User
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
- 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
- row 1
- TomMartens
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
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!
- 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 ) )
