PowerBI Cumulative Total / Duplicate Values

[sarahp]

Hi all,

I have been struggeling with this problem for quite a while now.

I’m trying to calculate a cumulative total of IssueId’s for each category. 

The formula I have now looks like this:

Cumulative Issues = 

Var TotalIssues = CALCULATE(COUNT(IssueStatus[IssueId]),ALLSELECTED(IssueStatus))

Var CurrentIssues = COUNT(IssueStatus[IssueId])

Var SummarizedTable =

summarize(

ALLSELECTED(IssueStatus),

(IssueCategoryHierarchy[Hierarchy_L2_Text]),

"XZ", COUNT(IssueStatus[IssueId])

)

Var CumulativeSum =

Sumx(

FILTER(SummarizedTable, [XZ] >= CurrentIssues ),

[XZ])

Return

CumulativeSum,

Basically it adds up everything correctly, but as soon as duplicate values appear, it adds them up at once instead of adding them separately.

Any help is appreciated. Thank you!

[lbendlin]

Add a small random number to your values to break the ties.

[sarahp]

Hi @lbendlin 

If i add a random number the statement and result look like this:

ISINSCOPE(IssueCategoryHierarchy[Hierarchy_L2_Text]),

Var TotalIssues2 = CALCULATE(COUNT(IssueStatus[IssueId]),ALLSELECTED(IssueStatus))

Var CurrentIssues2 = COUNT(IssueStatus[IssueId])

Var SummarizedTable2 =

summarize(

ALLSELECTED(IssueStatus),

(IssueCategoryHierarchy[Hierarchy_L2_Text]),

"XZ", [NumberOfIssueId]

)

Var CumulativeSum2 =

Sumx(

FILTER(SummarizedTable2, [XZ] >= CurrentIssues2 ),

[XZ] )

Return

CumulativeSum2,

[lbendlin]

A little less small? Does not need to be all positive either.

[sarahp]

Could you please give me an example code?

[v-deddai1-msft]

Hi @sarahp ，

Would you please show us the expected output that we can help you well? 

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

Best Regards,

Dedmon Dai

[lbendlin]

for example 

x - 0.05 + RAND()*0.1