cancel
Showing results for
Did you mean:
Regular Visitor

## Cumulative measure based on dynamic measure – Slicer problem

Hi, need some help with the following:

The Visual

The visual shows the ranking of items based on the last time they were sold ('worst' to 'best')

The ‘PartID’ field is a column from the material table – ‘Products’. The other three fields are measures.

The ‘Cumulative Gap’ measure is a running total of the ‘Gap From Last Transaction’ measure.

Without using a slicer, the visual shows correct results for all the fields.

The problem

When adding a slicer, and adding ALLSELECTED to the measures, the ‘Gap From Last Transaction’ results are correct but the ‘Cumulative Gap’ shows the whole total in each cell instead of running total (left image)

If I remove ALLSELECTED from the measures, the ‘Cumulative Gap’ works as expected, but the results of the ‘Gap From Last Transaction’ are incorrect (obviously, because I removed ALLSELECTED) (right image).

these are the measures (i used index column similar to date column):

Gap From Last Transaction =

VAR MaxIndex = MAX ( Sales [ Index ] )

VAR LastBuy = CALCULATE( MAX ( Sales [ Index] ) ,

FILTER (ALLSELECTED (Sales) ,

Sales [ PartID ] =  FIRSTNONBLANK( Products [ PartID ] ,  Products [ PartID ] ) ) )

RETURN

Total Gaps =  SUMX ( Products , [ Gap From Last Transaction ] )

Cumulative Gap =

CALCULATE ( [ Total Gaps ] ,

FILTER (

ALLSELECTED( Products [ PartID ] ), SUMX ( Products , [ Total Gaps ] )  <= [ Total Gaps ] ))

Rank =

CALCULATE ( RANKX ( ALLSELECTED ( Products [ PartID] ) ,[Gap From Last Transaction ] ,,DESC ) )

Tanks for the help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super Contributor

## Re: Cumulative measure based on dynamic measure – Slicer problem

@dpbi

Sample File

Regards

Victor

Lima - Peru

Lima - Peru

Proud to be a Datanaut!

3 REPLIES 3
Highlighted
Super Contributor

## Re: Cumulative measure based on dynamic measure – Slicer problem

@dpbi

Sample File

Regards

Victor

Lima - Peru

Lima - Peru

Proud to be a Datanaut!

Regular Visitor

## Re: Cumulative measure based on dynamic measure – Slicer problem

@Vvelarde

Hi Victor.

Many thanks for your fast response and sample file.

Definitely, one of the best features of Power Bi Desktop is this forum.

Best Regards,

Dan

Super Contributor

## Re: Cumulative measure based on dynamic measure – Slicer problem

@dpbi

Thank you, your very clear description of the scenario helped me a lot.

Regards

Victor

Lima - Peru

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!