cancel
Showing results for
Did you mean:
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

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

## 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.

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

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

## Re: Sum Accummulate with 2 columns

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!

## 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

## 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.

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

Regular Visitor

## Re: Sum Accummulate with 2 columns

Thanks this is the problem.

Best regards!!

Announcements

#### 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?

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

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

Top Solution Authors
Top Kudoed Authors