SUM using DAX for each year

[0Experience]

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.

[v-yanimei-msft · Accepted solution]

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.

[Mattiar]

Hi,

you can try with:

AnnualRateTotal =
CALCULATE (
    SUM ( DataTable[Rate per 100,000] )
)

[0Experience]

I tried but it doesn't work the way give. It takes the individual one, not the sum for each year. See the screenshot.

[Mattiar]

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?

[0Experience]

In my original table I have too many column but in this (output) table I have only these 3 columns

Thanks for your time, and for trying to help me.

[Mattiar]

Then it could be a relationship problem.

Do you have the data dimension that relates to the fact table with a one-to-many relationship?
Is the column on which you make the relation the classic given column?

Unfortunately my help without pbix is a bit limited. But hopefully we can find a solution to this anomaly.

[0Experience]

I didn't use any relationship. I have too many columns but they are on the same table (no columns are coming from other table as part of relationship).

Thanks again

[Mattiar]

Ahhhh now it's all clear.

If you only want to keep one table, writing it in dax is much more time-consuming and is a route I do not recommend.

The quickest and easiest way is to import a Calendar table and relate date_key of the calendar to the date in your table.
In the Power BI visual table, you insert the Year column of the Calendar dimension and use the formula I wrote above.

Then everything will work like clockwork.
If you need anything further I will send you screenshots.

[0Experience]

thanks @Mattiar for your ideas

As I mentioned, I need it using DAX for another purpose. First need to store this value in a calculated column and than need to  use it later,