cancel
Showing results for
Search instead for
Did you mean:
Member

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Calculated measure filtered by many columns

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

6 REPLIES 6
Community Support Team

## Re: Calculated measure filtered by many columns

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Calculated measure filtered by many columns

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

Community Support Team

## Re: Calculated measure filtered by many columns

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Calculated measure filtered by many columns

Community Support Team

## Re: Calculated measure filtered by many columns

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Calculated measure filtered by many columns

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

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 282 members 2,993 guests
Recent signins:
Please welcome our newest community members: