Forum Discussion
SUM using DAX for each year
Hello everyone,
I have a table with two columns: "Year" and "Sum of Data". Basically, the "Sum of Data" column shows the total amount for each year.
Now, I'm trying to get the same total using a DAX formula, which I'll need for future work. But here's the catch: the result I'm getting from the DAX formula doesn't match the automatic sum I'm getting from the "Sum of Data" column.
I've included the DAX formula below:
AnnualRateTotal = CALCULATE(
SUM(DataTable[Rate per 100,000]),
ALLEXCEPT(DataTable, DataTable[Year])
)
I've tried to recreate the issue using demo data, but surprisingly, the DAX formula works fine there. Unfortunately, I can't share the main data or the Power BI file. I'm sorry about that.
If anyone knows a DAX formula that can give me the same result as the "Sum of Data" column, I'd really appreciate your help.
Thank you so much for your help.
Hi 0Experience ,
Thank you for reaching out and providing a detailed description of your issue. Based on your question, I've tested your DAX (just by creating a test table and a new measure) and analyzed why. The specific data in the test table will be provided in a later step.
As the following picture, we can see the result of Measure and Sum of Sales is same.
So, I wonder if you've used a filter. Because if you've used a filter, it will show different results like the following picture.
The function in your formula is to keep the filter context on the Year column while removing all other filter contexts. I'm going to give you ways to make sure that your calculations are made explicit every year, and thus possibly avoid some contextual issues.
Please follow these steps:
1.Create the test table Table. The specific data is in the following picture.
2.Create visualizations. Add Year and SalesData to Columns, then select Sum.
3. Right-click Table, select New Measure and input:
SUM using DAX = SUMX( VALUES('Table'[Year]), CALCULATE(SUM('Table'[SalesData])) )Then, add SUM using DAX to Columns and the outcome is in the following picture.
Best Regards,
Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi,
you can try with:
AnnualRateTotal =
CALCULATE (
SUM ( DataTable[Rate per 100,000] )
)I tried but it doesn't work the way give. It takes the individual one, not the sum for each year. See the screenshot.
It seems to me that you are using more information within your table. Like there is more than just the year and the sum.
Can't you even take a screen shot of the filters and fields present?
Hi 0Experience ,
Thank you for reaching out and providing a detailed description of your issue. Based on your question, I've tested your DAX (just by creating a test table and a new measure) and analyzed why. The specific data in the test table will be provided in a later step.
As the following picture, we can see the result of Measure and Sum of Sales is same.
So, I wonder if you've used a filter. Because if you've used a filter, it will show different results like the following picture.
The function in your formula is to keep the filter context on the Year column while removing all other filter contexts. I'm going to give you ways to make sure that your calculations are made explicit every year, and thus possibly avoid some contextual issues.
Please follow these steps:
1.Create the test table Table. The specific data is in the following picture.
2.Create visualizations. Add Year and SalesData to Columns, then select Sum.
3. Right-click Table, select New Measure and input:
SUM using DAX = SUMX( VALUES('Table'[Year]), CALCULATE(SUM('Table'[SalesData])) )Then, add SUM using DAX to Columns and the outcome is in the following picture.
Best Regards,
Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
