Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I have been using this running total measure for several years now and never had any problem with the result, until now.
But when I add the field 'Ordernumber' from the same table 'fact' the running total (cumulative) is not working anymore. I really do not understand why? I hope some of you do;) please enlighten me!
Hi @amitchandak
This first measure with ALL does work, but I want to be able to filter on a Ordernumber and then it does not show the correct amount. The second measure with ALLSELECTED is almost correct!
The only thing is that the amount in yellow are correct, but only for the last row, very strange.
@RH-BIP ,
Try this measure:
RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALL ( fact ), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
VALUES ( fact[ordernumber )
)
I hope this helps!
Mark this as a solution if it answers your queation. Kudos are always appreciated.
Thanks!
The measure not working, because the ALL includes the Fact and therefore I cannot filter the calendar. And could you maybe explain why I need tho add the Values part. I never had to use it before to get the correct outcome.
RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALL ( fact ),calendar[Datetime] <= MAX ( calendar[Datetime] ));
VALUES ( fact[ordernumber )
)
Hi @RH-BIP ,
Sorry for the typo.
Try this:
RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALL ( calendar), calendar[Datetime] <= MAX ( calendar[Datetime] ) );
VALUES ( fact[ordernumber )
)
Also here, the VALUES() puts the Ordernumber into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Ordernumbers.
Thanks
That is not working unfortunatly..
Even without ordernumber it is not working
When I remove the values part it does, but not with the ordernumber..
@Tanushree_Kapse , @RH-BIP , Based on what I got so far. So if take order number from fact and running total logic from the calendar then it running total within the order number. Value for *19 and *20 repeated for null value rows .
Try
RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALL ( fact), fact[Datetime] <= MAX ( fact[Datetime] ) );
)
or
RunningTotal =
CALCULATE (
SUM ( fact[Quantity] ),
FILTER ( ALLSELECTED ( fact), fact[Datetime] <= MAX ( fact[Datetime] ) );
)
Hi @amitchandak
The second measure is almost correct, but as you can see the cumulative is not working correctly in some cases. See the yellow amounts where only the last of the three is correct.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |