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.
Hello everybody,
yes, another running total question. I think, I miss something in my formula or just think the wrong way.
My tables look like this:
Table 1:
Order ID
Value
Client
Signed Date (Date)
Country
Segment
Table 2:
Country
Segment
Planned Value
Date (Month)
Table 3 (Time Dimension):
Date
Month
Quarter
Year
What I want to do:
I want to calculate my running totals for both of my tables per month and then display them in one diagram (to see, if I hit or miss my planned value for this month.
Important/Problem: At the same time I want to have the "Segment" as the legend. So that I can see which segments take the biggest part of my values.
--> Whenever I do this, it just adds my segment values to the previous "totals".
E.g.
January total:
500 - Segment A
400 - Segment B
100 - Segment C
1000 - Total
Then it shows me the Value "2000" for January.
My current Formula
Running Total =
CALCULATE (
SUM ( 'Table 1 (signed date) 2018'[Value] );
ALL ( 'Table 1 (signed date) 2018');
'Table 1 (signed date) 2018[Signed Date]' <= EARLIER ( 'Table 1 (signed date) 2018'[Signed Date] )
)
Goal:
Be able to see the running total per month / quarter for Table 1 and Table 2 while being able to display by Segment.
I hope I could explain myself correctly...
Thanks in Advance
Best Regards
Fatih
Hello @Anonymous,
Can you please share sample data or sample pbix file?
Regards.
Hello @Anonymous,
Please try the following measure:
Running Total =
TOTALYTD(SUM('IS - Order'[Value]),'Date'[Date])
Hello @rajulshah,
thank you for your proposal.
Unfortunately, as our initial data is a bit more complicated and we didn't have time to try further solutions - we sticket to a more work intensive python solution.
But I also tried your proposal and unfortunately didn't receive the results I expected. The Values don't add up as wished.
Kind Regards
Hello @rajulshah
here is the pbix and the simplified excel. Had to create a new document...
https://www.dropbox.com/sh/050srwrcla9iixp/AAAURf12dPVuJRf5eqwdPvE4a?dl=0
Regards
Fatih
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |