Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Everyone,
Hoping you can help me write a measure for cumulative running total, but by an Index column rather than a date. I would like a measure for cumulative total of the ONHAND QTY column in the attached image. Note, this table is filterable by Item No, so the returned dataset could have an index of 775-792, for example.
Thanks so much for your help! Note, ONHAND QTY is a column, but I can replace it with a measure if I need to.
Solved! Go to Solution.
Hi @CoreyP ,
This is a context problem, when you add the measure in your table since you have other columns within the visual and all of the fields belong to the same table the context changes so you need to apply the filter no only to the INDEX column but to all other columns in this case you need to redo your measure to:
Cumulative =
CALCULATE (
SUM ( 'Pick sequence'[ONHAND_QTY] );
FILTER (
ALLSELECTED (
'Pick sequence'[Pick Sequence];
'Pick sequence'[PALLET_ID];
'Pick sequence'[Item N.º]
);
'Pick sequence'[Pick Sequence] <= MAX ( 'Pick sequence'[Pick Sequence] )
)
)
Check PBIX file attach I added a slicer for pallet s you can try to select different values and check that the cumulative still is calculated.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi there community,
Hoping to get some guidance on my 3rd measure to calculate the Cumulative Total by index:
In my first measure, I'm calculating Bank Submissions:
Bank Submissions =
DISTINCTCOUNTNOBLANK('Ongoing & Unpaid Data Revised'[Policy No#])
In my second measure, I'm calculating the Previous Index Churn:
Previous Index Churn =
VAR CV = [Bank Submissions]
VAR D = SELECTEDVALUE( 'Ongoing & Unpaid Data Revised'[Index] ) - 1
VAR E =
CV - CALCULATE(
[Bank Submissions],
'Ongoing & Unpaid Data Revised'[Index] = D && CV <> BLANK(),
ALLSELECTED( 'Ongoing & Unpaid Data Revised'[Index] ))
RETURN
ABS(E)
Then in my 3rd measure, I'm trying to get the Cumulative Churn:
Cumulative Churn =
CALCULATE (
[Previous Index Churn],
FILTER (
ALLSELECTED ( 'Ongoing & Unpaid Data Revised'[Index]),
'Ongoing & Unpaid Data Revised'[Index] <= MAX('Ongoing & Unpaid Data Revised'[Index]
)
))
This is not giving me the desired results, but instead I'm just getting a repeat of the total:
Hi @BICrazy ,
Maybe you need to try the following measure:
Cumulative Churn =
SUMX (
TOPN (
MAX ( 'Ongoing & Unpaid Data Revised'[Index] ),
SUMMARIZE (
'Ongoing & Unpaid Data Revised',
'Ongoing & Unpaid Data Revised'[Index],
"PreviousChurn", [Previous Index Churn]
)
),
[PreviousChurn]
)
Be aware that without any data is difficult to know if this is the correct syntax.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thanks for the suggestion. This measure didn't get the cumulative totals. This is what I got:
I'm basically looking to sum the Previous Index Churn e.g.
6982 + 5876 = 12858
12858 + 3664 = 16522
16522 + 2623 = 19145
19145 + 2203 = 21348 .....and so forth
Hi @BICrazy ,
Can you please send me a mockup?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @BICrazy ,
You should use the following measure:
Cumulative_Churn_1 =
SUMX (
FILTER (
SUMMARIZE (
ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
'Ongoing & Unpaid Data Revised'[Index],
"@IndexChurn", [Previous Index Churn]
),
'Ongoing & Unpaid Data Revised'[Index]
<= MAX ( 'Ongoing & Unpaid Data Revised'[Index] )
),
[@IndexChurn]
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
This works like a charm. Just that the 29517 on Index 0 is the base. I'd like to start the count from Index 1 onwards.
Hi @BICrazy ,
Should the first value be 6982 or 36499?
If you want to have the 6.982 then use the measure:
Cumulative_Churn_ > 0 =
SUMX (
FILTER (
SUMMARIZE (
ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
'Ongoing & Unpaid Data Revised'[Index],
"@IndexChurn", [Previous Index Churn]
),
'Ongoing & Unpaid Data Revised'[Index]
<= MAX ( 'Ongoing & Unpaid Data Revised'[Index] ) && 'Ongoing & Unpaid Data Revised'[Index] > 0
),
[@IndexChurn]
)
If you don't want the 0 but want the basis sum use the following:
Cumulative_Churn wthout 0 =
IF( MAX('Ongoing & Unpaid Data Revised'[Index]) <> 0, SUMX (
FILTER (
SUMMARIZE (
ALL ( 'Ongoing & Unpaid Data Revised'[Index] ),
'Ongoing & Unpaid Data Revised'[Index],
"@IndexChurn", [Previous Index Churn]
),
'Ongoing & Unpaid Data Revised'[Index]
<= MAX ( 'Ongoing & Unpaid Data Revised'[Index] )
),
[@IndexChurn]
))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Starting value is 6982. Thanks so much for your help. This is exactly what I was looking for. Much appreciated.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis measure simply returns the same value.
Hi @CoreyP ,
This is a context problem, when you add the measure in your table since you have other columns within the visual and all of the fields belong to the same table the context changes so you need to apply the filter no only to the INDEX column but to all other columns in this case you need to redo your measure to:
Cumulative =
CALCULATE (
SUM ( 'Pick sequence'[ONHAND_QTY] );
FILTER (
ALLSELECTED (
'Pick sequence'[Pick Sequence];
'Pick sequence'[PALLET_ID];
'Pick sequence'[Item N.º]
);
'Pick sequence'[Pick Sequence] <= MAX ( 'Pick sequence'[Pick Sequence] )
)
)
Check PBIX file attach I added a slicer for pallet s you can try to select different values and check that the cumulative still is calculated.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @CoreyP ,
Try the following change in the code:
Cumulative =
CALCULATE (
SUM ( 'Pick Sequence'[ONHAND_QTY] ),
FILTER ( ALL ('Pick Sequence'[Pick Sequence] ), 'Pick Sequence'[Pick Sequence] <= MAX ( 'Pick Sequence'[Pick Sequence] )
))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUnfortunately, that does not work either.
I've kinda all but given up. I keep googling and re-writing and re-googling and re-googlewriting and now I just think it's a tad too advanced. Or, what's probably more likely, I'm not advanced enough to write it.
Hi,
Share the link from where i can download your PBI file.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |