Pivot table filter value with greater than x slicer

[Anonymous]

Hi all,

I have a somewhat large table (close to 1M rows) that I'm trying to analyze using a Pivot Table. The rows section shows me the customer ID and the values are the project volume amongst other things. I created a really nice looking interactive dashboard and i was was wondering if it was possible to use slicers to filter a pivot table by any of the value filters?

My first approach was the following: 

I created a helper table on another sheet, which simply contains a series of values (starting from 0 to 500k in 100k steps) and a name. The value names are simply used for the slicer for aesthetic reasons. For the helper table I created a slicer so that I can filter the values.

Then I created a measure in the actual customer table, which gives me the MIN value from the helper table

Min_Param_X =MIN(ParameterX[Param_X])

 and a second measure that only shows values if greater than the MIN 

Project_volume_GreaterThanX

=IF(
[Project_volume]>=[Min_Param_X],
[Project_volume],
0
)

Finally, I filter the values if they are qualified to 0.

Two problems with this approach:
1. the MIN function shows me the MIN value of the helper table not only for the visible values (even if it is filtered, the MIN value remains 0)

2. the actual table does not react and does not update itself from the helper table after using the silcer

 Can anyone tell me if this is generally possible? Where is my thinking error? Is there possibly a smarter approach to reach the goal?

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a what if parameter from 0 to 500 increased by 100.

Then try this measure:

Measure = IF(MAX('Table 1'[Project Volume])>=SELECTEDVALUE(Parameter[Parameter]),MAX('Table 1'[Project Volume]),0)

Apply this measure to the target table visual by setting measure=0.

When select one value in slicer, it shows:

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

Hi @v-gizhi-msft , @amitchandak 

sure, I hope dropbox works as well.  Here you go: Sample File 

I am grateful for any indication about my problem! Thank you

[Anonymous]

Hi @amitchandak 

Thank you very much for your answer. I tried your approach, but it did not work. 

I'm not sure I'm missing anything. For this reason I have uploaded a sample file that shows my basic problem (sample file ). Again I try to use the table below to create a way to filter the projects in the actual table using the silcer. 

Please give me feedback if something is not clear. I hope for some advice. 

Thanks and best regards

[v-gizhi-msft]

Hi,

Due to the consideration on Security, could you please share your sample by OneDrive for Business?

Best Regards,

Giotto

[Anonymous]

Again, the main problem is that the MIN function in the measure does not calculate the MIN of the visible values in the pivot table, but the MIN of all available values.

Is there a way to calculate the MIN value based on the filter criteria, i.e. only the available values?

I am grateful for any advice

[amitchandak]

@Anonymous , if this min is not coming from whatif parameter then make it mix and use it as var in the other formula

Min_Param_X =MINX(ParameterX,ParameterX[Param_X])