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

[dreyz64]

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

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

Thanks!

[MFelix]

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.

[dreyz64]

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.

[MFelix]

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.

[dreyz64]

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 😞

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

)

)

[MFelix]

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?

[dreyz64]

HI Felix

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

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

[MFelix]

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.

[dreyz64]

Hi Felix,

Managed to find a work around:

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

Here is the sample.

Thanks for your help!

[MFelix]

Hi @dreyz64 ,

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

[dreyz64]

Hi Felix,

Totals should be the sum of the row for each measure

[MFelix · Accepted solution]

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

[dreyz64]

Thank you Felix, it seems to be working.
Would you mind explain to me why it was not working before and what the Summarize and SumX do?

I understand more or less but I don't get why it was not working 😞

[MFelix]

Hi @dreyz64 

SUMMARIZE returns a summary table for the requested totals over a set of groups. In you case I created a temporary table with dates and the measure calculation to act as my calculation base this will change the line context since instead of getting 10 lines perdate I'm only getting one.

SUMX it's an aggregation formulathat returns the sum of an expression evaluated for each row in a table, in your case what I'm doing is picking up the values for each of the lines for previous table and summing them is like having this calculation made:

Line 1 - 10 - Acumulated - 10

Line 2 - 20 - Acumulated - 30

Line 3 - 10 - Acumulated - 40

In the end I return the 40 if I'm on total level or 10 or 20 if I'm on line level.

Row context is very important when calculating measures and this can be change just by adding or changing filter, columns, measures and all sort of data to your visualizations.

[MFelix]

The table should give similar results as the one on top of the report?

[dreyz64]

The Table on the top is with all information. All % Changes regardless of the threshold.

I would like to achieve the table at the bottom where i only see values where % changes is bigger than the threshold in absolute.

I wanted to do this with a filter but could not manage. So now I am summing up each measure if they are respecting the condition abs(% Change) > Threshold.
However the totals are all wrong.