cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpbi Regular Visitor
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.

 

Capture1.PNG

 

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

 

Capture2.PNG

 

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

LastBuy - MaxIndex + 1

 

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
Vvelarde Super Contributor
Super Contributor

Re: Cumulative measure based on dynamic measure – Slicer problem

@dpbi

 

Hi, please review this PBIX.

 

Sample File

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Vvelarde Super Contributor
Super Contributor

Re: Cumulative measure based on dynamic measure – Slicer problem

@dpbi

 

Hi, please review this PBIX.

 

Sample File

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Highlighted
dpbi Regular Visitor
Regular Visitor

Re: Cumulative measure based on dynamic measure – Slicer problem

@Vvelarde

 

Hi Victor.

Many thanks for your fast response and sample file.

Your solution works perfectly! 

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

Best Regards,

 

Dan

Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors