cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dreyz64
Frequent Visitor

Help - Filtering a visualization based on a parameter and a measure not working

Hi everyone,

 

I now have been struggling for a while with this and I am going mad as I'm sure this is something simple.

I have created a table where I have various products, their actual sales, forecasted sales and the % difference between actual sales and forecasted sales (%Change).

I have created a parameter (0-100%) and what I want to do now is to filter the below table so that only the %Change (in absolute value) that are bigger than my parameter value get shown.

 

The filter that I have created is like this:

Type: Column --> Filter =  IF(divide(Parameter[Parameter Value],100)<=abs([%Change]), TRUE,FALSE)

However it is not working in my table. As you can see below, when i plot in my table 'Test filter 3' I get correct values but 'Filter' is wrong.
Test Filter 3 = divide(Parameter[Parameter Value],100)<=abs([%Change])

2020-02-12_16-14-01.jpg

 

What am I doing wrong? Is it because my columns are dates and thus my filter is calculated differently than my measure?

Thanks!

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

This as to do with context I have made a single measure the actuals:

 

Actuals_Final = SUMX(SUMMARIZE(Sheet1;Sheet1[DATE];"@ACTUALS";[ACTUALS2]);[@ACTUALS])

 

This is additional on top of the ACTUALS2 so you need to have a intermidiate measure to calculate the values.

 

Please check if is working correctly on the PBIX file.

 

If it works you need to do a similar thing for forecast. and then use those measures on the Change %2


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


View solution in original post

15 REPLIES 15
Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

Is your parameter a % column (formatt as text) or a number formated as percentage?

 

If it's the last one you should compare with 1 and not with 100, because 100% is just a visual format the actual number is 1.


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


dreyz64
Frequent Visitor

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @MFelix ,

My parameter is an integer and then when I do my filter is do divide(parameter, 100) so in the end, it is between 0 and 1.

But for my users they can directly input the whole number.

Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

can you please share a sample file? is difficul to see based on your image what are the formats values etc. used, and when using measure the context is the most important part.

 

Changing the context of a measure can make very different result than what we are expecting.


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


dreyz64
Frequent Visitor

Re: Help - Filtering a visualization based on a parameter and a measure not working

Unfortunately I cannot share a sample as I have confidential data and it would take me ages to make a sample report to share...

I've managed to find a work around where instead of trying to use a Filter, I re-calculate my values 

For example, i do: IF abs(%Change ) > Threshold (in %) then sum (actuals)

Same for my forecast values.

This allows me to get the below table.
Now visually the table does what I wanted the filter to do. However the totals are completely messed up and I don't understand why 😞

2020-02-12_17-05-51.jpg

Example of formula: 

Actuals = IF(abs([%Change_Calc])>=divide(Parameter[Parameter Value],100),
sum('COT_ACT_Forecast Bridge'[ActVol]), 0)
%Change_Calc =
IF(
sum('COT_ACT_Forecast Bridge'[ActVol])=0 && Sum('COT_ACT_Forecast Bridge'[Fcst])<>0, -1,
IF(
sum('COT_ACT_Forecast Bridge'[ActVol])<>0 && Sum('COT_ACT_Forecast Bridge'[Fcst])=0,1,
DIVIDE((sum('COT_ACT_Forecast Bridge'[ActVol])-sum('COT_ACT_Forecast Bridge'[Fcst])),sum('COT_ACT_Forecast Bridge'[ActVol]),BLANK())
)
)
Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

This is refering to the context of your measures, you probably need to use an aggregator function (SUMX) instead of a sum but this depends on the way your model is setup.

 

Can you make a mockup file with sample data and expected resutl? or share the file trough private message?


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


dreyz64
Frequent Visitor

Re: Help - Filtering a visualization based on a parameter and a measure not working

HI Felix

I don't find a way to send attachements...

Any idea what I can do? Company blocks dropbox and other sharing sites.

Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

As you refer it's trough a dropbox, onedrive type of link.

 

Do you know if it's possible the we tranfers link on your company policies? It's also a chance.


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


dreyz64
Frequent Visitor

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi Felix,

Managed to find a work around:

https://wetransfer.com/downloads/56b29ea7acd5260932b17b2d99087d1f20200212200248/c45de59623548a85bd31...

Here is the sample.

Thanks for your help!

 

Super User III
Super User III

Re: Help - Filtering a visualization based on a parameter and a measure not working

Hi @dreyz64 ,

 

Can you please tell me what are the corrects value that you should get for the information in the table?


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors