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]) )
Solved! Go to Solution.
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])))
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.
Share the link from where i can download your PBI file.
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.
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
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.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps