cancel
Showing results for
Did you mean:
New Member

## Total Calculado sem considerar o filtro

Olá pessoal, tenho uma tabela cujo o total automático calculado pelo power bi altera conforme filtros. Quero ver os últimos 3 anos, porém, o total gostaria que fosse do período completo. Tem como fazer isso? Ou seja, a tabela me mostra 2021, 2020, 2019, mas o total considerar ainda 2018, 2017 por exemplo.

Hi guys, I have a table whose automatic total calculated by power bi changes according to filters. I want to see the last 3 years, but the total would like it to be the full period. Is it possible to do this?  That is, the table shows me 2021, 2020, 2019, but i need the total to still considering 2018, 2017 for example.

1 ACCEPTED SOLUTION
Super User

@blackavipowerbi
Hi, you basically create running total:

``````TOTAL_Past3Years =
//var oldYears = CALCULATE(SUMX(FILTER('Year','Year'[Year] IN {2017,2018}),'Year'[Total]),ALL('Year'[Year]))
//var Latest3Years = CALCULATE(SUM('Year'[Total])+oldYears,FILTER('Year','Year'[Year]<=MAX('Year'[Year])))
var maxYear = MAX('Year'[Year])
var Cumulative = CALCULATE(SUM('Year'[Total]),ALL('Year'[Year]),'Year'[Year]<=maxYear)
return Cumulative``````

7 REPLIES 7
New Member

The only thing is: the past 3 years is showing on each colum...

Super User

I set it up as a general solution, please provide sample data and the expected result and I can try to figure it out.

THanks

New Member

I want something more live below - each year in a colum, and the last colum the total (desconsidering filters as you provided me the solution).

New Member

Got it!!!

New Member

The 'Year'[Total] means what? Sorry about it.

TOTAL_Past3Years =
var oldYears = CALCULATE(SUMX(FILTER('Calendário FC','Calendário FC'[Ano FC] IN {2017,2018}),'Year'[Total]),ALL('Year'[Year]))
//var Latest3Years = CALCULATE(SUM('Year'[Total])+oldYears,FILTER('Year','Year'[Year]<=MAX('Year'[Year])))
var maxYear = MAX('Calendário FC'[Ano FC])
var Cumulative = CALCULATE(SUM('Year'[Total]),ALL('Calendário FC'[Ano FC]), 'Calendário ATD'[Ano]<=maxYear)
return Cumulative
Super User

It's just how I called the column for the value for the Sum for the specific Year.
Normally, it would be prolly called "Sum" or something.

Super User

@blackavipowerbi
Hi, you basically create running total:

``````TOTAL_Past3Years =
//var oldYears = CALCULATE(SUMX(FILTER('Year','Year'[Year] IN {2017,2018}),'Year'[Total]),ALL('Year'[Year]))
//var Latest3Years = CALCULATE(SUM('Year'[Total])+oldYears,FILTER('Year','Year'[Year]<=MAX('Year'[Year])))
var maxYear = MAX('Year'[Year])
var Cumulative = CALCULATE(SUM('Year'[Total]),ALL('Year'[Year]),'Year'[Year]<=maxYear)
return Cumulative``````

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors