topic Re: Calculation issues - weird grouping based on the numerical values in DAX Commands and Tips

Calculation issues - weird grouping based on the numerical values

my_microsoftacc — Wed, 12 Jun 2024 14:04:42 GMT

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

Re: Calculation issues - weird grouping based on the numerical values

lbendlin — Thu, 13 Jun 2024 00:19:38 GMT

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-Forum/ba-p/963216

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/1447523

Re: Calculation issues - weird grouping based on the numerical values

Anonymous — Fri, 14 Jun 2024 06:38:19 GMT

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.

Re: Calculation issues - weird grouping based on the numerical values

my_microsoftacc — Tue, 18 Jun 2024 08:38:32 GMT

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)