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
Super User
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

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
Super User
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

vojtechsima_0-1635862546968.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!