Calculate LASTNONBLANK tab with formula

[mathieu_thelot]

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 ?

[Baskar]

Can u please explain little bit clear.

based on my understand,

Do you want last updates sales based on date field like this

[KHorseman]

Duplicate thread. See answer here.

ratio_tot = CALCULATE( [ratio] ;
my_tab[time] = LASTNONBLANK(my_tab[time];1)
)

[mathieu_thelot]

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 ?

[Baskar]

Can u please explain little bit clear.

based on my understand,

Do you want last updates sales based on date field like this ?

[mathieu_thelot]

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   6

2/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 

[Eric_Zhang · Accepted solution]

---

@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   6

2/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

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

[mathieu_thelot]

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. 

[KHorseman]

Oops, I forgot that using a function like LASTNONBLANK makes it a complex filter. You have to put it in an actual filter statement.

ratio_tot = CALCULATE( [ratio] ;
FILTER(my_tab; my_tab[time] = LASTNONBLANK(my_tab[time];1))
)

[KHorseman]

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)
)