Forum Discussion
SUM Values if quarter equal to
Hi I am using power query to manipulate some data. I have a "Quarter" column and a "Total Amount" column. I want to get the sum of the "Total Amount" column if the "Quarter" column is equal to a criterion. For example, if the value in "Quarter" is Q1-2017, and there are 15 values in the "Total Amount" column matched to Q1-2017, I want to sum only those values in Total Amount.
Right now I am using the following formula in power query:
if [Quarter] = "Q2-2017" then List.sum(#"Changed Type"[Total Amount]) else null
This formula, however, is giving the total sum of all quarters regardless of the conditional pointing to only Q2-2017. Please advise! Thank you!
- Hi andash,
In the query editor you need to select the option groupby, the. Select the quarter column and on the aggregate part select the column you want and sum.
This will give you the aggregation per each quarter.
Be aware that this step will make a table with only 2 columns quarte and the sum of the value you need.
Regards,
3 Replies
- az38
Community Champion
Hi andash
it doesnt look like a good idea to calculate this in power query.
i could advice you to use an easy DAX-statement measure
Measure = CALCULATE( SUM('Table'[Total Amount]); FILTER(ALL('Table');'Table'[Quarter] = "Q2-2017") )do not hesitate to give a kudo to useful posts and mark solutions as solution
- MFelix
Super User
Hi andash,
In the query editor you need to select the option groupby, the. Select the quarter column and on the aggregate part select the column you want and sum.
This will give you the aggregation per each quarter.
Be aware that this step will make a table with only 2 columns quarte and the sum of the value you need.
Regards,Thank you! This is exactly what I needed.
