Forum Discussion
Calculate LASTNONBLANK tab with formula
mathieu_thelot wrote:
I have a table, and I want to visualise the last update of groups.
time Group x y
1/1 A 2 5
2/1 A 4 10
3/1 A 5 15
1/1 B 4 62/1 B 5 9
1/1 C 3 5
2/1 C 4 8
3/1 C 5 12
4/1 C 9 16
I want to visualise the ratio_tot = x/y for each group
All groups have a different last_update time.
I want to have something like that:
time Group ratio_tot
3/1 A 5/15
2/1 B 5/9
4/1 C 9/16
KHorseman , that's the idea:
ratio_tot = CALCULATE( [ratio] ;
my_tab[time] = LASTNONBLANK(my_tab[time];1)
)but that does'nt work :
LastNonBlank use a True/False expression to filter table
Two measures to get the expected output in a Matrix visual.
Max time = MAX(my_tab[time]) ratio_tot = CALCULATE(sum(my_tab[x])&"/"&sum(my_tab[y]), FILTER( my_tab, my_tab[time]= MAX(my_tab[time]) ))
Or you're requiring a calculated table?
newTable = ADDCOLUMNS(FILTER(my_tab,RANKX(FILTER(my_tab,my_tab[Group]=EARLIER(my_tab[Group])),my_tab[time],,DESC)=1),"xxx",my_tab[x]&"/"&my_tab[y])
If you have any question, feel free to
Microsoft Employee
mathieu_thelot wrote:
I have a table, and I want to visualise the last update of groups.
time Group x y
1/1 A 2 5
2/1 A 4 10
3/1 A 5 15
1/1 B 4 62/1 B 5 9
1/1 C 3 5
2/1 C 4 8
3/1 C 5 12
4/1 C 9 16
I want to visualise the ratio_tot = x/y for each group
All groups have a different last_update time.
I want to have something like that:
time Group ratio_tot
3/1 A 5/15
2/1 B 5/9
4/1 C 9/16
KHorseman , that's the idea:
ratio_tot = CALCULATE( [ratio] ;
my_tab[time] = LASTNONBLANK(my_tab[time];1)
)
but that does'nt work :
LastNonBlank use a True/False expression to filter table
Two measures to get the expected output in a Matrix visual.
Max time = MAX(my_tab[time]) ratio_tot = CALCULATE(sum(my_tab[x])&"/"&sum(my_tab[y]), FILTER( my_tab, my_tab[time]= MAX(my_tab[time]) ))
Or you're requiring a calculated table?
newTable = ADDCOLUMNS(FILTER(my_tab,RANKX(FILTER(my_tab,my_tab[Group]=EARLIER(my_tab[Group])),my_tab[time],,DESC)=1),"xxx",my_tab[x]&"/"&my_tab[y])
If you have any question, feel free to
Thank you for this post, that's really helpfull Eric
For the measures that's perfect
Do you know if it's possible to custom the total line ?
other than average, median, max
Actually, I use an other way, but that's so boring and long ...
I have a visual element with good meauses for groups and without total line ,
and an other visual element with the global measure of my ratio.
I've got question about your table, Eric_Zhang
when you use :
newTable = ADDCOLUMNS(FILTER(my_tab;
RANKX(FILTER( my_tab;
my_tab[Group]=EARLIER(my_tab[Group]));
my_tab[time];;DESC)=1);"xxx";my_tab[x]&"/"&my_tab[y])
Is it necessary to have a single table with all measures, and data ?
In my case, I have my dataset with all data,
in an other table I have my unique_value = my name groups
the 2 tables are connected and that's more praticale to do that for my project.