cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Sum Accummulate with 2 columns

Hello

 

Im need to create a accumlated column grouped by 2 criteria. In my case group by A_Codi and Month

   

2017_06_16_20h37_25

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Sum Accummulate with 2 columns

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.

1.PNG

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.

1.PNG

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.

6.PNG5.PNG4.PNG

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
Highlighted

Re: Sum Accummulate with 2 columns

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]



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper I
Helper I

Re: Sum Accummulate with 2 columns

Hello

 

Thanks is very strange!

 

I have this model

 

Diagram.png

 

 

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

 

captura3.png 

 

But use the a_codi from customer table, the table show correctly but the graph no!! 

 

Don't understand

 

Captura2.png

 

Thanks!

Highlighted

Re: Sum Accummulate with 2 columns

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Microsoft
Microsoft

Re: Sum Accummulate with 2 columns

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.

1.PNG

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.

1.PNG

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.

6.PNG5.PNG4.PNG

Best Regards,
Angelia

View solution in original post

Highlighted
Helper I
Helper I

Re: Sum Accummulate with 2 columns

Thanks this is the problem. 

 

Best regards!!

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors