Forum Discussion
Dynamic sumx () specific vales from a table
Hi all,
How can I have a dax sumx() function that only sums specific values with specific characteristics?
Desire to have
| I'm attempting to have a dynamic sumx() function where only sums data that have specific characteristic. The SubRegion_shift can be changing by month.
|
Current result
| Ronas= sumx('Fact - Tbl.all.SMALL, 'Fact - Tbl.all.SMALL'[RONAS]) acd= sumx('Fact - Tbl.all.SMALL, 'Fact - Tbl.all.SMALL'[acd]) |
Data example
|
|
regards |
|
For my particular circumstance, I decided to add an extra column to the query by adding a line of multiple CASE WHEN functions to the sql query.
8 Replies
perezco I haven't read all your conditions, so just hoping to provide you the idea that you can apply to your own data:
SUMX ( FILTER ( TableName, TableName[Column1] = "Condition1" && TableName[Column2] = "Condition 2") , TableName[ColumnToSum] )
If your conditions change from month to month, are you able to create a mapping table or another column that uses those conditions to flag whether to include the row yes or no in the SUMX, then use that flag column in your FILTER condition.
You may also be interested in using an OR filter which is || in Power BII:
https://excelwithallison.blogspot.com/2021/09/advanced-filter-or-vs-and.html
Thanks,
That section has already been covered.example:
CALCULATE(sumx('table','table'[RONACALLS]),fltr_Qtr,fltr_Weekday_flag,fltr_subregion_shift, FILTER(VALUES('table'),'table'[SubRegion] = "NA"))perezco If that's already covered, then what is your question exactly?
The excel table with the conditions, if you can make that more tabular database friendly. For example a column with all the values in your IN {{value1, value2}} conditions, each value needs to be its own cell in the same column. Then you can use DAX VALUES() function and the IN operator in DAX to create those conditions and make them dynamic by getting your raw data condition table set up properly.
