Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
blackavipowerbi
Regular Visitor

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
Memorable Member
Memorable Member

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

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

Got it!!!

blackavipowerbi
Regular Visitor

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
Memorable Member
Memorable Member

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.