cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sebasjun Regular Visitor
Regular Visitor

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
Microsoft v-huizhn-msft
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

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.
sebasjun Regular Visitor
Regular Visitor

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!

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.
Microsoft v-huizhn-msft
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

sebasjun Regular Visitor
Regular Visitor

Re: Sum Accummulate with 2 columns

Thanks this is the problem. 

 

Best regards!!

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors