Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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
The only thing is: the past 3 years is showing on each colum...
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).
Got it!!!
The 'Year'[Total] means what? Sorry about it.
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.
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |