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
Im need to create a accumlated column grouped by 2 criteria. In my case group by A_Codi and Month
Im create a measure with this formula
Acumulated = CALCULATE(sum(Query1[Total_USD]);FILTER(ALL(Query1);Query1[Mes]<max(Query1[Mes])))
But not run!!
I need Accomulated has this values
A_Codi Month Acumulated Total_USD
28812 February -40.60 -40.60
28812 March -40.60 0
36100 April -35.44 -35.44
..
36246 January 0 0
36246 February 0 0
36246 March -2396.00 -2396.00
36246 April 2248,63 4644,63
36246 May 3075.31 826.68
36246 June 3379.3 303.99
Thanks
Best regards
Solved! Go to Solution.
Hi @sebasjun,
For your result, the cumulated measure is still not sure based on my understanding. For the following example, 54805107<60963705, it shoud be running total.
You can create a calculated column using the formula, transfer the month text type to number type.
New-Month=SWITCH([Month], "January", 1,"February",2, "March", 3, "April", 4, "May", 5, "June",6, "July",7, "August", 8 , "September", 9 , "October", 10,"November",11, "December", 12, 0 )
Then create a measure using the formula.
Acumulated = CALCULATE(sum(Query1[Total_USD]);FILTER(ALLEXCEPT(Query1,Query1[A_Codi]);Query1[New-Month]<max(Query1[New-Month])))
I test it using my sample table below, it works fine.
Create a measure:
Acumulated = CALCULATE(SUM('FACT'[REVENUE]),FILTER(ALLEXCEPT('FACT','FACT'[ID]),'FACT'[Month]<=MAX('FACT'[Month])))
Please see the result shown in the following screenshot.
Best Regards,
Angelia
What other columns do you have in your table? Do you have a month number? Or do you have a calendar table? If you have a month number you can use The same formula you wrote but replace [Mes] with [month number]
Hello
Thanks is very strange!
I have this model
The table customer is related with Query 1. And I use the table Customer in all report with other graphs
If I use the A_Codi from query. The table and the graph show correctly
But use the a_codi from customer table, the table show correctly but the graph no!!
Don't understand
Thanks!
Hi @sebasjun,
For your result, the cumulated measure is still not sure based on my understanding. For the following example, 54805107<60963705, it shoud be running total.
You can create a calculated column using the formula, transfer the month text type to number type.
New-Month=SWITCH([Month], "January", 1,"February",2, "March", 3, "April", 4, "May", 5, "June",6, "July",7, "August", 8 , "September", 9 , "October", 10,"November",11, "December", 12, 0 )
Then create a measure using the formula.
Acumulated = CALCULATE(sum(Query1[Total_USD]);FILTER(ALLEXCEPT(Query1,Query1[A_Codi]);Query1[New-Month]<max(Query1[New-Month])))
I test it using my sample table below, it works fine.
Create a measure:
Acumulated = CALCULATE(SUM('FACT'[REVENUE]),FILTER(ALLEXCEPT('FACT','FACT'[ID]),'FACT'[Month]<=MAX('FACT'[Month])))
Please see the result shown in the following screenshot.
Best Regards,
Angelia
Thanks this is the problem.
Best regards!!
Sorry, I can't help you with the information you have provided. If you post a sample workbook set up and clearly showing the issue, then I will take a look for you
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |