How can I make my measures dynamic with a slicer?

[Burtoninlondon]

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.

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

[DataNinja777]

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, 

[Burtoninlondon · 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