Forum Discussion
Helper IIIDAX Help to get sum by week
Hello, Community,
I am looking for some help on how to populate a calculated column based on a function I want to create. I added a screenshot of the result I am looking for. What I want the function to do is sum 3 coumns I have in my model and based on the week number sum by that week and insert the result of that week with the three different columns into a different column named 'Weekly Sum'
ElvirBotic Does this NEED to be a calculated column? You could use a measure:
SUM Measure = SUMX(Table, Table[Pass] + Table[Caution] + Table[Fail])
Then create a matrix visual with Week in Rows and SUM Measure in values.
Creating it as a calculated column is possible too, just need to use some filters and be aware of evaluation context, and then be careful how and where you use it, otherwise you may end up amplifying your results:
SUM Column = SUMX(FILTER(Table, Table[Week] = EARLIER(Table[Week]) , Table[Pass] + Table[Caution] + Table[Fail])
3 Replies
- AllisonKennedy
Community Champion
ElvirBotic Does this NEED to be a calculated column? You could use a measure:
SUM Measure = SUMX(Table, Table[Pass] + Table[Caution] + Table[Fail])
Then create a matrix visual with Week in Rows and SUM Measure in values.
Creating it as a calculated column is possible too, just need to use some filters and be aware of evaluation context, and then be careful how and where you use it, otherwise you may end up amplifying your results:
SUM Column = SUMX(FILTER(Table, Table[Week] = EARLIER(Table[Week]) , Table[Pass] + Table[Caution] + Table[Fail])
ElvirBoticDoes not need to be a calculated column. it was more if it is doable was my thought process. Thank you for this. What would be your recommendation for using different measures to calculate a different result to convert into a percentage?
- AllisonKennedy
ElvirBotic If I'm understanding your question, I would do a SUM of Pass and use the DIVIDE function with the SUM Measure as denominator for a Pass Percentage as a MEASURE. Does that work?