I’m trying to create a virtual table using summarize function with allselected on date dimension table but the cumulative total does not seem to be working.
Expected Result:
Actual Result:
Can anybody explain to me why Allselected does not seem to be working in Summarize function and how to correct it?
Attached is the power bi file.
Summarize and Allselected.pbix (87.8 KB)
Hi Zzy,
Let’s try the simple way for the time being 
Like the following measure:
Cumulative Total - ALLSELECTED =
IF(
ISBLANK( [Total Amount] ),
BLANK(),
CALCULATE(
[Total Amount],
FILTER(
ALLSELECTED( DimDate ),
DimDate[Date] <= MAX( DimDate[Date] )
)
)
)
Summarize and Allselected_v2.pbix (88.9 KB)
If you need anything else, just drop a line.
Have fun with DAX & EnterpriseDNA forum 
Mariusz
Thank you Mariusz. But my question is how come the Allselected() does not seem to be working when nested inside the Summarize() function to create a virtual table.
This is the issue:
@Zzy,
What you’ve created is not a virtual table - it’s a physical table created by a DAX expression. I’m not trying to be a stickler for language here - they’re two very different things. A virtual table exists only in memory (for example, a table variable in a measure), and can only be connected within the data model by a virtual relationship using TREATAS. A physical table actually exists within the data model - you can view it, connect it with active or inactive relationships, basically do anything you can with any other physical table (except manipulate it in Power Query). You can also use TREATAS to connect it.
Your allselected cumulative calculation isn’t working because the table you are operating on is unconnected to the data model, and thus is blind to any selections you are making in the slicers.
I hope this is helpful.
I don’t think there is a need to create relationship here.
How do you explain this:
I created this new “physical” summary table with no connection relationship with the DimLocation table.But I’m still able to calculate the total amount of North region.
Summarize and Allselected.pbix (89.9 KB)
Hi @Zzy,
OK. Let’s try different approach
The following table was created using SUMMARIZE:
[MonthnYear] was added to sort the table.
UsingAllSelected_2 =
FILTER(
SUMMARIZE(
DimDate,
DimDate[MonthInCalendar],
DimDate[MonthnYear],
"@Total Amount", [Total Amount],
"@Cumulative", CALCULATE(
[Total Amount],
FILTER(
ALLSELECTED( DimDate ),
DimDate[MonthnYear] <= MAX( DimDate[MonthnYear] )
)
)
),
[@Total Amount] > 0
)
You can use it in a virtual table, like this one:
UsingAllSelected_2 VT =
VAR VT = FILTER(
SUMMARIZE(
DimDate,
DimDate[MonthInCalendar],
DimDate[MonthnYear],
"@Total Amount", [Total Amount],
"@Cumulative", CALCULATE(
[Total Amount],
FILTER(
ALLSELECTED( DimDate ),
DimDate[MonthnYear] <= MAX( DimDate[MonthnYear] )
)
)
),
[@Total Amount] > 0
)
VAR Result = MAXX( VT,[@Cumulative] )
Return
Result
In this case the measure is returning MAX from the column “@Cumulative” just to show that the virtual table is doing the job.
The result.
If this is not what you are looking for, could you please explain your assumptions a little further?
Summarize and Allselected_v3.pbix (79.0 KB)
Have a good day or night!
Mariusz
Thanks Mariusz! That’s exactly what I’m looking for.
@Zzy,
How do you explain this:
I created this new “physical” summary table with no connection relationship with the DimLocation table.But I’m still able to calculate the total amount of North region.
Sure – you can do that by hard coding “North” into your filter condition as you done above. My point was that without a relationship, either actual or virtual, your physical UseAllSelected table will have no “awareness” of the selected slicer values. You can see that below and in the attached file:
Summarize and Allselected (1).pbix (88.9 KB)