Forum Discussion
Helper IICumulative not working with sumx
I have a Table with few columns such as member id, start date, end date , provider name etc, value etc...my value column is a calculated column with value=1 formula. so this column contains 1 for all rows.
Requirement is to calculate "sum of value for distinct member id ". if i use sum(Table[value]), cumulative is working fine but it gives wrong value. so, i used sumx(values(Table[member id]), calculate(max(Table[Value]))).
But this measure is not making cumulative properly
can someone please help me to achieve this
Hi Vamshi2020
I think you want to calculated the sum for each member id.
Due to I don't know your data model, I build a sample to have a test.
Sample:
I think you want to get Sum for ID 1 = 2, Sum for ID 2 =1, Sum for ID 3 =2.
Measure:
Cumulative = SUMX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),'Table'[Value])Result is as below:
Calculated column:
Sum value for each id = SUMX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Value])Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- parry2k
Super User
Vamshi2020 Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 Hi Vamshi2020
I think you want to calculated the sum for each member id.
Due to I don't know your data model, I build a sample to have a test.
Sample:
I think you want to get Sum for ID 1 = 2, Sum for ID 2 =1, Sum for ID 3 =2.
Measure:
Cumulative = SUMX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),'Table'[Value])Result is as below:
Calculated column:
Sum value for each id = SUMX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Value])Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Vamshi2020
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
