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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors