Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Burtoninlondon
Frequent Visitor

How can I make my measures dynamic with a slicer?

Hello,

 

I have a basic dashboard in which I want to calculate the running total of a set of values, calculate the high water mark and calculate the difference from the high water mark. This is a snapshot of my dashboard.

 

Burtoninlondon_0-1720865784198.png

 

The data comprises values from two data sets and I want the calculations to update based on the selection of each individual data set or both combined.

 

I have created a measure to calculate the Running Total as below. This calculation works.

Running Total = 
CALCULATE (
    [Total Values],
    FILTER ( ALL ( 'Data' ), Data[Index] <= MAX ( Data[Index] ) )
)

 

The High Water Mark is the highest value in the running total until a new high is reached. For example:

  • if the 1st value makes the Running Total equal 10 then the High Water Mark will be set to 10;
  • if the 2nd value makes the Running Total equal 5 then the High Water Mark will remain as 10;
  • if the 3rd value makes the Running Total equal 15 then the High Water Mark will be set to 15;

I have created a measure to calculate the High Water Mark as below. This calculation works.

High Water Mark = 
MAXX (
    ADDCOLUMNS (
        FILTER ( ALL ( 'Data' ), Data[Index] <= MAX ( Data[Index] ) ),
        "Running Total", [Running Total]
    ),
    [Running Total]
)

 

The Difference from High Water Mark is the difference between the High Water Mark and the Runnnig Total. I have created a measure to calculate this as below. This calculation works.

Difference from High Water Mark = [Running Total] - [High Water Mark]

 

The challenge I have is that when I select a single data set the Running Total, High Water Mark & Difference from High Water Mark calculations remain based on the combined data set (Dataset1 & Dataset2). 

 

I have an index column to order the individual data values as there are instances where individual data values ocurr on the same date. I wondered if the solution was to somehow create a dynamic measure which would be either an index calendar which would comprise the individual data values from the selected data set which I could then reference in my Running Total and High Water Mark measures. 

 

I am not an experienced user of Power BI and I am unable to resolve this issue with my curent level of knowledge therefore if anyone in the community can help I would greatly appreciate it.

 

I don't know how to attach my PBIX template but this is a wetransfer link to it...https://we.tl/t-LSBoXYMD3i 

 

Thank you for any help you can provide.

 

Neil

1 ACCEPTED SOLUTION

Hi @DataNinja777 , thank you for your response.

 

I didn't need to refer to the link you kindly shared as I realised that in my filters I had selected ALL instead of ALLSELECTED. I changed to ALLSELECTED and it works as intended.

 

Thanks,

 

Neil

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

Hi @Burtoninlondon ,

 

A widely used technique to make the measures dynamic with a slicer selection is to create a harvester measure in combination with a disconnected slicer created from a disconnected table.  I attach a classic article on this topic.

https://p3adaptive.com/dynamic-topn-reports-via-slicers-part-2/

Best regards, 

Hi @DataNinja777 , thank you for your response.

 

I didn't need to refer to the link you kindly shared as I realised that in my filters I had selected ALL instead of ALLSELECTED. I changed to ALLSELECTED and it works as intended.

 

Thanks,

 

Neil

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.