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.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

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

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!