Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello everyone,
I'm seeking advice on how to generate a cumulative column within the query model, - dealing with a virtual rather than a physical table.
For instance, I encounter an error when attempting to use the following formula. I've marked my desired outcome in red in the attached example, but I'm uncertain about the correct approach to achieve this result. Any guidance would be greatly appreciated.
Solved! Go to Solution.
Hi Amitchandak,
Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else.. thanks!
Thanks
Cupid
Hi @CupidC ,
You can try this
DemoTable =
VAR vTable =
DATATABLE (
"Index",INTEGER,
"Name", STRING,
"Value", INTEGER,
{
{ 1,"A", 1 },
{ 2,"A", 2 },
{ 3,"A", 3 }
}
)
VAR vAddRT =
ADDCOLUMNS (
vTable,
"RT",
VAR thisrowindex = [Index]
RETURN
SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
)
RETURN
vAddRT
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @CupidC ,
Here is a dax expression that does not use the earlier function:
Accumulation =
CALCULATE(
SUM('Table'[VALUE]),
FILTER(
ALLEXCEPT('Table','Table'[ID]),
'Table'[VALUE] <= MAX('Table'[VALUE])
)
)
Final output
For more information about the operation of accumulation you can refer to this blog
How to Calculate Cumulative Values with DAX - Microsoft Fabric Community
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Albet
Thanks,, but the table is created virtually, so i can't reference "Table" in query mode, do you have other ways to achieve the result..?
Hi @CupidC ,
You can try this
DemoTable =
VAR vTable =
DATATABLE (
"Index",INTEGER,
"Name", STRING,
"Value", INTEGER,
{
{ 1,"A", 1 },
{ 2,"A", 2 },
{ 3,"A", 3 }
}
)
VAR vAddRT =
ADDCOLUMNS (
vTable,
"RT",
VAR thisrowindex = [Index]
RETURN
SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
)
RETURN
vAddRT
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Amitchandak,
Thank you! It's working perfectly. I have another question: is there a possibility to use a variable instead of using Earlier? I've come across several posts recommending against this function and was wondering if there's an alternative method involving variables or something else.. thanks!
Thanks
Cupid
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |