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
I want to apply a measure to the lastupdate values of my data.
to have a better comprehension, I have like 3 groups:
All groups ended at different time.
my measure is :
ratio = sum(val) / sum(other_val)
... I use sum() function, that's not really elegant ...
and I apply the ration measure to multigroups, at the last update :
ratio_tot = CALCULATE( [ratio] ;
LASTNONBLANK(my_tab[time];1)
)
This function return "blank" value
how could I apply correctly what I expect ?
Is there a way to apply a LASTNONBLANK filter to a table ?
Is there an other alternative to use arg1 in CALCULATE method when arg1 is not a measure? Did any function could do that ?
If I understand what you're asking for correctly, the formula should be
ratio_tot = CALCULATE( [ratio] ;
my_tab[time] = LASTNONBLANK(my_tab[time];1)
)Can u please explain little bit clear.
based on my understand,
Do you want last updates sales based on date field like this ?
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
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
