Forum Discussion

gcam032's avatar
gcam032
Helper I
5 years ago
Solved

Cumulative % with legend and missing values

Hi All,

 

Update - PBIX file : https://www.dropbox.com/s/gwbh6fsor9qlpn5/cumulative%20test.pbix?dl=0

 

I've seen many questions about this, but nothing that has a legend field as well - so I'm a little lost.

 

Here is the table, you can see that the cumulative value jumps to the total when there is no value for the score_band variable:

 

For example, the row of score_band = 700 and 2021 04 should be 8 rather than 136 as it shows.

 

Here's the DAX:

 

 

test = CALCULATE (
    SUM(Summary[session_count]),
    FILTER (
        ALLSELECTED (Summary),
        Summary[score_100_bin] >= MIN (Summary[score_100_bin])
    ),
    VALUES(DateDim[Month Year])
)

 

 

Need Help

10 Replies

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Not the perfect answer and i cannot catch my own mistake but you may be able to improvise on this.  Download the PBI file from here.

        Hope this helps.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    gcam032 

    is this what you want?

    test2 = if(ISFILTERED(DateDim[Month Year]),if(ISBLANK(SELECTEDVALUE(summarytable[score_100_bin])),sum('summarytable'[session_count]),sumx(FILTER(all(summarytable),(summarytable[score_100_bin]<=max(summarytable[score_100_bin])||ISBLANK(summarytable[score_100_bin]))&&RELATED(DateDim[Month Year])=max(DateDim[Month Year])),summarytable[session_count])),if(ISBLANK(SELECTEDVALUE(summarytable[score_100_bin])),sum('summarytable'[session_count]),sumx(FILTER(ALL(summarytable),(summarytable[score_100_bin]<=max(summarytable[score_100_bin])||ISBLANK(summarytable[score_100_bin]))&&RELATED(DateDim[Last2andMTD])=TRUE()),summarytable[session_count])))

    • gcam032's avatar
      gcam032
      Helper I
      5 years ago

      Hi ryan_mayu ,

       

      Thanks for that.  It's close.  Firstly, the cumulation needs to start at 1000 and accumulate to 0 (as I had in my pbix).  Where you have missing values, I want the running total.  For example, the row for 700 for 2021 04, using your figures I'd want that to have a 144 rather than a blank.