Forum Discussion
Calculated measure filtered by many columns
HI,
In this table I want to calculate the volume of product used by a user. We see the initial volume in [pmp_total] and this pmp decrease when the user is working in [pmp_prod].
So for each distinct [nopile] and each distinct noopt (user ID), I need to know how many volume.
For this I think I have to use the column «Type» who give me when a new [nopile] (PILEIN) and when the user is replaced (NEWOPT).
In this case, the user 53 use 582 - 510 = 72.
The user 52 use 510 - 258 = 252 and the user 27 use 258 - 1= 257.
6 Replies
Hi Anonymous,
I think you can write a formula to compare 'pmp_total' and maximum 'pmp_prod' volume of current user, if it less than 'pmp_total', find out the diff between maximum 'pmp_prod' of current user and minimum 'pmp_prod' of next user; otherwise get the diff between 'pmp_total' and first 'pmp_prod' of current user.
Sample measure:
Measure = VAR currUser = SELECTEDVALUE ( Table[Noopt] ) VAR currJob = SELECTEDVALUE ( Table[noprodjob] ) VAR pmp_prodlist = CALCULATETABLE ( VALUES ( Table[pmp_prod] ), FILTER ( ALLSELECTED ( Table ), [noopt] = currUser && [noprodjob] = currJob ) ) VAR pmp_total = MAXX ( FILTER ( ALLSELECTED ( Table ), [noprodjob] = currJob ), [pmp_total] ) VAR maxOfCurrent = MAXX ( pmp_prodlist, [pmp_prod] ) RETURN IF ( maxofCurrent < pmp_total, MINX ( FILTER ( ALLSELECTED ( Table ), [noprodjob] = currJob && [pmp_prod] > maxOfCurrent ), [pmp_prod] ) - maxOfCurrent, pmp_total - MINX ( pmp_prodlist, [pmp_prod] ) )Regards,
Xiaxin Sheng