cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blackavipowerbi
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
vojtechsima
Resolver III
Resolver III

@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

vojtechsima_0-1635862546968.png

 

View solution in original post

7 REPLIES 7
blackavipowerbi
New Member

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

 

blackavipowerbi_1-1635863659248.png

 

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

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

 

blackavipowerbi_0-1635864144048.png

 

blackavipowerbi
New Member

Got it!!!

blackavipowerbi
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

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.

vojtechsima
Resolver III
Resolver III

@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

vojtechsima_0-1635862546968.png

 

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors