Cumulative % with legend and missing values

[gcam032]

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])
)

[ryan_mayu]

@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]

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.

[ryan_mayu]

@gcam032 

I suggest you remove empty values in the bin.

please see the attachment below

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[gcam032]

Hey @Ashish_Mathur , Thanks - I edited and added a link to an example with the same issue.

[Ashish_Mathur]

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.

[gcam032]

Thanks for your attempt.  Unfortunately, I'm not sure it's useful as the figures are incorrect.  I'll wait and see if anyone else has a solution

[Ashish_Mathur · Accepted solution]

Hi,

Download my revised PBI file from here.

Hope this helps.

[gcam032]

Hey @Ashish_Mathur ,

This appears to work!  Although, I don't understand at all what is occuring.  Is there any chance you can write me a breif explanation of what is happening here?  Thanks!

[Ashish_Mathur]

You are welcome.  Please spend some time on each measure and if you still face problems with understanding, post back.