Calculated measure filtered by many columns

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous,

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

[Anonymous]

Thanks for your time!

https://www.dropbox.com/s/gp6mcs0gpamyaqm/Ebout_test.pbix?dl=0

[Anonymous]

Hi @Anonymous,

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

[Anonymous · Accepted 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