Forum Discussion
Cumulative Help (All vs. Allselected)
Hi Everyone,
I'm currently struggling a lot with a cumulative measure.
I have a need to report on delivered items, delayed items and planned items. The cumulative chart should show the previous two weeks and the upcoming two weeks and it should be a cumulative total and the cumulation should start from the first visible date. I also need to be able to use slicers on this visual.
At the moment, I have my cumulative measure and i've filtered my graph to show the previous and upcoming two weeks via a custom column which i've brought in as a visual filter. However, i am now running into issues where i cannot filter my graph using a category slicer. When i try to slice the data, it only shows it at a cumulated level.
To resolve this, i was told to use 'AllSelected' rather than 'All'. When i do this, my data doesn't add up correctly.
I have attached the sample data and PBIX file which contains a comparison between what happens when i use 'ALL' vs 'ALLSELECTED'. The result yeilded from the measure using 'ALL' is what i believe to be correct.
Is anyone able to help me understand how i can make this work. Thank you so much in advance. Any insight is appreciated.
Hi,
Thank you for your question.
I don't understand the whole data better than you do, but if I may say what you asked,
The visualization restricts the date range and only shows from March 1st.
-> allselectect: it does not count the previous value that is before March 1st. It shows also the correct value, perhaps, I guess that you want to see the result that also contains before-March-1st values. (Before March 1st, I could see that there were 2 results, but if you use allselect, this 2 values would not be counted)
-> all: it does count the previous value (2 values that happened before March 1st) but shows it from March 1st because the visualization restricts the SHOWING.So, if you want to consider the value that is also coming from before March 1st, I suggest you use ALL.
If you want to see 2 values that come from before March 1st, using ALLSELECT is not the choice for you. However, the reason why you were suggested to use ALLSELECT previously is because, if I may guess, you used ALL for the Sheet1-table and you wanted to have a slicer for the project name. In this case, if you do not look into the measure correctly, and if you just be suggested to use ALLSELECT, it will not give you the correct result.
Sorry to write too long. Thank you very much for reading.
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.
5 Replies
Hi,
Please correct me if I wrongly understood.
When I see your measures for calculating a cumulate total, I saw the below.
FILTER(ALL(Sheet1), Sheet1[Date] <= MAX(Sheet1[Date]))
Instead of applying ALL to Sheet1-table, try to apply ALL to the other table that is the calendar table. Because in your visualization, the x-axis (date) is the date column that came from the other table, not from the Sheet1-table.
Also, please make sure that your calendar table is assigned as a Calendar-Table.
Thanks for your response.
My issue at the moment is I need to be able to filter my line and bar chart with the project slicer I have up top. To do this, I've been told I need to use the ALLSELECTED function rather than ALL. I have tried to use ALLSELECTED however, as you can see on my file, I get two different results when I use ALL vs ALLSELECTED. I believe this might be because I'm using a custom date column to filter my visual. I'm thinking maybe ALLSELECTED is removing the filter context, not sure if I'm right though??
I'm not sure how else to show cumulative total where my line chart dynamically shows past and future two weeks with DAX and I can also slice my data correctly
Hi,
Thank you for your feedback.
Please correct me if I wrongly understood, but as I mentioned,
Visual X-Axis is describing Query1[Date] + showing another filter in the filter pane.
ALL or ALLSELECTED inside measures -> describing Sheet1
In this case, if I am allowed to suggest, please try to match the context that you want to visualize and calculate. Then, I believe you can compare more appropriately between ALL and ALLSELECTED.
ALL & ALLSELECTED are used for Sheet1, so in this case, all products are adding up. In case ALL, Top-slicer is only slicing for visual, not calculation. So, Allselect might look like working.The below picture is what I could show the difference between ALL and ALLSELECTED.
The link to the pbix file is down below.
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.https://drive.google.com/file/d/18pSvoTDbkpCZ7UJ0KkYHArmaOOR6UWed/view?usp=sharing