cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gcam032
Helper I
Helper I

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:

gcam032_0-1621978004129.png

 

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

 

 

1 ACCEPTED SOLUTION

Hi,

Download my revised PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
ryan_mayu
Super User II
Super User II

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

@gcam032 

I suggest you remove empty values in the bin.

please see the attachment below

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Ashish_Mathur
Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

Download my revised PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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!

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors