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 all,
I need your help, I have a table similar to this style but I need to do the cumulative revenue.
TOTAL INVESMENT | QUARTER | FY |
$ 288,521 | Q4 | FY17 |
$ 151,918 | Q1 | FY18 |
$ 35,850 | Q2 | FY18 |
$ 61,265 | Q3 | FY18 |
$ 202,410 | Q4 | FY18 |
my final table should be left with a column like this
TOTAL INVESMENT | QUARTER | FY | Cumulative |
$ 288,521 | Q4 | FY17 | |
$ 151,918 | Q1 | FY18 | $ 440,439 |
$ 35,850 | Q2 | FY18 | $ 476,289 |
$ 61,265 | Q3 | FY18 | $ 537,554 |
$ 202,410 | Q4 | FY18 | $ 739,964 |
I used this formula but it does not work:
Solved! Go to Solution.
Hi @Anonymous,
It seems that you want to calcualted the cumulative per quarter, you could try the steps below.
1. Create an index column in query editor;
2. Create the measure with the formula below.
Measure = CALCULATE ( SUM ( 'Table1'[ACUMM] ), FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Index] <= MAX ( 'Table1'[Index] ) ) )
Here is you desired output.
Best Regards,
Cherry
Hi,
I'm assuming in your data you have actual date values somewhere in addition to just the Quarter and FY text values. I think something along the lines of this calc will work for you. I took your dataset and plugged it into excel, and added a random date column that corresponded with the Q and FY (just made an assumption that the fiscal year was a traditional calendar year):
I then used the following calc:
Thanks for the help. apply this same method that you indicate me but when I take the info to only see by Q and FY it stops showing the accumulated
I think you'll need to leverage a date column here, rather than just those two other columns.
Hello ,
I continue with my problem, because the idea is to see the data summarized by Quarter and fiscal year, but by placing between my elements the Q this fails and if I set the FY values separates me, I only summarize each Q according to the year.
I need to be able to have something like this:
YEAR QUARTER KEY REVENUE ACUMM
FY17 | Q1 | FY17Q1 | $ 5,989 | $ 5,989 |
FY17 | Q2 | FY17Q2 | $ 5,689 | $ 11,678 |
FY17 | Q3 | FY17Q3 | $ 487 | $ 12,165 |
FY17 | Q4 | FY17Q4 | $ 5,589 | $ 17,754 |
FY18 | Q1 | FY18Q1 | $ 3,645 | $ 21,399 |
FY18 | Q2 | FY18Q2 | $ 11,223 | $ 32,622 |
FY18 | Q3 | FY18Q3 | $ 5,647 | $ 38,269 |
FY18 | Q4 | FY18Q4 | $ 3,365 | $ 41,634 |
FY19 | Q1 | FY19Q1 | $ 1,200 | $ 42,834 |
also if I take these values to a dynamic table, have the option to see them accumulated independent of the filter that is:
FY18Q1 | FY18Q2 | FY18Q3 | FY18Q4 | FY19Q1 | |
Reven Acumm | $ 21,399 | $ 32,622 | $ 38,269 | $ 41,634 | $ 42,834 |
I hope to make myself understood. Thank you so much for your help
Hi @Anonymous,
It seems that you want to calcualted the cumulative per quarter, you could try the steps below.
1. Create an index column in query editor;
2. Create the measure with the formula below.
Measure = CALCULATE ( SUM ( 'Table1'[ACUMM] ), FILTER ( ALLSELECTED ( 'Table1' ), 'Table1'[Index] <= MAX ( 'Table1'[Index] ) ) )
Here is you desired output.
Best Regards,
Cherry
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |