find max and min value from same column obtained from dynamic slicer selection and find difference

[sivarajan21]

Dear all,

I need to create a dax for dynamically selecting sites from the slicer and find the difference from max & min selected.

For example, in above screenshot, I have selected 3 sites, out of which max is 316.46 and min is 31.55. so I need a measure

to return the difference and calculate the performance % 

dax measure diff = 316.46 - 31.55 

performance measure = dax measure diff/max(dax measure diff)

Could you please guide me on this?

Regards,

SC

[lbendlin · Accepted solution]

Here is a sample of the pseudo code:

performance measure = divide(maxx(table,units)-minx(table,units),maxx(allselected(table),units)-minx(allselected(table),units))

you can refine that with SUMMARIZE(ALLSELECTED())  etc.  Really depends on how you want to interpret it.

[lbendlin · Accepted solution]

Here is a sample of the pseudo code:

performance measure = divide(maxx(table,units)-minx(table,units),maxx(allselected(table),units)-minx(allselected(table),units))

you can refine that with SUMMARIZE(ALLSELECTED())  etc.  Really depends on how you want to interpret it.

[sivarajan21]

Hi,

Thank you so much for the response and this works brilliantly for the Data profile. But now the issue I have is with the below graph  viz 'Half-Hourly Profile'. This measure is not showing the performance at half hourly level? FYR, please see the below screenshot.

Thanks in advance

[lbendlin]

what is the expected outcome based on the data you provided?

[sivarajan21]

Hi,

My sincere apologise for not giving the expected outcome. so if we see the below screenshot

This shows the units at half hourly level. Now according to the tooltip shown above performance measure gives value '0.00'. but the expected performance for corresponding date(in this case 08/20/2022) and time(in this case 12:00:00 AM) should be 3.95-2.99 =0.96 .

So 0.96 is the required performance at 12:00:00 AM and this should change according to the half hourly time. for example at 12:30:00 it should be (3.86-3.56=0.3 ). The date used for this visual (x-axis) is merged date from data profile table. Also, this visual should change dynamically when we move the date slicer(Date field from calendar table) as shown below

 FYR, i have again shared the updated data file in below location 

https://onedrive.live.com/?id=EA584763CE840515%21737&cid=EA584763CE840515

Please feel free to let me know if you need further info

Thanks in advance

[lbendlin]

Please check the OneDrive link - it comes up empty for me.

[sivarajan21]

Hi,

Apologise, PFA link for the file

Profile Explorer (1) - Copy (1).pbix

Can you please confirm, if you are able to access this?

Please free to let me know if you are unable to access

Thanks

[lbendlin · Accepted solution]

performance measure = 
var mmax = CALCULATE(max(DataProfile[Units]),allselected(Contacts[Name]))
var mmin = CALCULATE(min(DataProfile[Units]),allselected(Contacts[Name]))
return mmax-mmin

[sivarajan21]

Hi,

Many thanks & I will also accept this as a solution so that it will help others. 

Today i was handling a data and had a query.

I am trying to create a dax in my report which i am unable to do. This is a different one so i have raised this as a new query. Can you please guide me through that if you don't mind. The link to that is 

find the dates in a column that is not present in ... - Microsoft Power BI Community

Thanks in advance

[sivarajan21]

Hi,

Many thanks for this beautiful solution and you cracked it.

This works like a charm. Many thanks for patiently guiding me on this and have noted this for future reference.

Once again thanks a lot sir

Kind regards,

Siva

[sivarajan21]

Hi,

Apologise & PFA link to sample file location

https://onedrive.live.com/?id=EA584763CE840515%21737&cid=EA584763CE840515 

I want to give additional info on the same:

The Relationship is and the fields used in dax measure are below:

My requirement is, I want to find max & min and show the difference in tooltip. for example, when i select 2 sites from slicer as shown in below screenshot

it should return 263.31-215.11= 48.2 in site variance tooltip but currently it shows zero.

but what i observed is when i turn this into table visual instead of line chart,it shows the difference in total level as below

Can you please guide me on this issue? How can show this difference total

in line chart tooltip?

Thanks in advance

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[sivarajan21]

Hi,

Apologise, PFA LINK https://onedrive.live.com/?id=EA584763CE840515%21737&cid=EA584763CE840515 

thanks

[lbendlin]

Please check the link, it requires authentication.

[sivarajan21]

Hi,

Apologise, i am sharing my one drive, can you please try this link

Profile Explorer (1) - Copy.pbix

if not, please let me know your email id or a link to a location where i can save this

Thanks