Calculation issues - weird grouping based on the numerical values

[my_microsoftacc]

Hi everyone, 

I have a database of different companies' capacity per year.

The measures I created are:

• YTD Capacity: calculates the accumulated capacity up until the year before the filtered year. 

YTD Capacity = CALCULATE(sum('Table'[Capacity]),filter(all('Table'[Year]),'Table'[Year]<Max('Table'[Year])))

• Total capacity: calculates the total capacity of all years.

Total Capacity = calculate(sum('Table'[Capacity]),all('Table'[Year]))

However, for some reason, PowerBI calculates the sum based on the distinct values of the capacity. As seen in pic below, e.g: year 2015 with capacity of 120 is one group, year 2016 - 2019 with the same capacity of 150 is one group, capacity of 153 is one group etc..

The data shown belongs to one company.

Does anyone know what might be the reason for it and how to fix it? Thanks

[Anonymous]

Hi @my_microsoftacc ,

I create a table as you mentioned.

I think you can change your DAX codes, here is my DAX code.

YTD Capacity1 = 
CALCULATE(
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Year] < MAX('Table'[Year])
        ),
        'Table'[Capacity]
    )
)

Total Capacity1 = 
SUMX(
    ALL('Table'),
    'Table'[Capacity]
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[my_microsoftacc]

Hi thanks for your response but unfortunately it does not work. Now it sums all capacity of all products and all companies I have in my database (I have more than one product, one company)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...