Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi,
I found an easier way to do this. In power query I added an index and a caluclated column to get the expecting result:
Thanks
Hi @FRG,
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
Wow, what a complex formula!
Except for user #53 I got negative number if I filter this nopile. For this user the result is exact.
Thanks
Hi @FRG,
It sounds like I miss some conditions to ignore calculation on some scenarios. Can you please share a pbix file with part of sample data for test and modify formula?
Regards,
Xiaoxin Sheng
Hi @FRG,
You can try to use below measure if it works, I modify my logic and add 'pmp_total' as one of filter condition:
Measure = VAR currUser = SELECTEDVALUE ( 'sysebout_jour piletrx'[Noopt] ) VAR currJob = SELECTEDVALUE ( 'sysebout_jour piletrx'[noprodjob] ) VAR pmp_total = MAX ( 'sysebout_jour piletrx'[pmp_total] ) VAR pmp_prodlist = CALCULATETABLE ( VALUES ( 'sysebout_jour piletrx'[pmp_prod] ), FILTER ( ALLSELECTED ( 'sysebout_jour piletrx' ), [noopt] = currUser && [noprodjob] = currJob && [pmp_total] = pmp_total ) ) VAR maxOfCurrent = MAXX ( pmp_prodlist, [pmp_prod] ) VAR minOfCurrent = MINX ( pmp_prodlist, [pmp_prod] ) RETURN IF ( maxofCurrent < pmp_total, MINX ( FILTER ( ALLSELECTED ( 'sysebout_jour piletrx' ), [noprodjob] = currJob && [pmp_total] = pmp_total && [pmp_prod] > maxOfCurrent ), [pmp_prod] ) - minOfCurrent, pmp_total - MINX ( pmp_prodlist, [pmp_prod] ) )
Regards,
Xiaoxin Sheng
Hi,
I found an easier way to do this. In power query I added an index and a caluclated column to get the expecting result:
Thanks
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |