Forum Discussion

Natalia2024's avatar
Natalia2024
Regular Visitor
2 years ago

Cumulative sum measure

Hello,

 I have the following cumulative sum measure which is built like that:

ArticleRank = RANKX(ALL(vdimarticle_v2),[Calculation KPI],,DESC,Dense)
PercentageofTotal = 
var TotalKPI = CALCULATE([Calculation KPI],ALL(dimarticle))
RETURN
 DIVIDE([Calculation KPI],TotalKPI)
CumulativeSumTop102 = 
var Top10Table = CALCULATETABLE(
                                  dimarticle_v2,
                                  FILTER(
                                    ALL(dimarticle_v2),
                                    [ArticleRank]<=30
                                  )
)
var CurrentArticleSum = [PercentageofTotal]
RETURN
    CALCULATE(
        [PercentageofTotal],
        FILTER(
            Top10Table,
            [PercentageofTotal]>=CurrentArticleSum
        )
    )

It works as follows, it ranks articles based on quantity and then it takes quantity of the first article and divides it by total quatity, for second article, it takes quantity of the first article + quantity of the second article and then  divides it by total quantity etc.

But I have problem if I filter sth on table dimarticle, because than measure shows incorrect numbers, do You know how to avoid this situation so allow users filtering on the table and make this cuulativesum be adjusted accordingly to the filters?

Thank you in advance,

Natalia

2 Replies

  • hi Natalia2024 

     

    try to modify you All(table) to AllSelected(table) so that the table works based on your slicers/filters. if still not work, share your dataset with sample data and desired output scenarions.

    • Natalia2024's avatar
      Natalia2024
      Regular Visitor

      Thank you so much for the response but ALLSELECTED didn't work in this case, I rewrote this measures a bit differently and now it works.