How to dynamically exclude data from bar chart

[paulfromhouston]

I use parameter to dynamically selecting the data to display on the bar chart. But the bar chart is sometimes too big to be be snapshot for ppt. So I need to exclude some data out of the bar chart. I am looking for two solutions: one is a filter which set a range to filter out, for example, +/-100 need to be excluded; the second solution is to display only the top 10 (for example) and bottom 6 (for example) on the bar chart, and I can change the numbers to meet my needs, so i expect there are two box filters which may specify top m column and bottom n colum that are desired. 

Please help!

Thanks,

Paul 

[paulfromhouston · Accepted solution]

Here is the solution:

1) make a table, "Exclusion Range", the data column can be generated by generateseries(-20000,20000,5)

2) bring the table of Exclusion Range to a slicer, slicer setting should be "Betweeen" 

3) Create a measure, Value Bigger Than = max('Exclusion Range[Value]')

4) create a second measure, Value Smaller Than = min('Exclusion Range[Value]')

5) Place these two measures into the measures used in Parameter field, for example, 

Net MBOE = CALCULATE(SUM('OTSD Reconciliation - Working'[NetMBOE]), FILTER('OTSD Reconciliation - Working', sum('OTSD Reconciliation - Working'[NetMBOE])> [valueBigger Than] || sum('OTSD Reconciliation - Working'[NetMBOE])<[Value Smaller Than]))

6) Reset the slicer of exclusion range, which will exclude those range from bar chart. 

Thanks everybody!

Paul

[paulfromhouston]

I have found a solution, will share it later today.

[Anonymous]

Thanks for Ashish_Mathur's concern about this issue.

Hi, @paulfromhouston 

I'm not sure I understand you correctly: by +/-100 do you mean that only data from -100 to +100 are displayed in visual? Like this:

If I understand you correctly, you can change your Measure to the following:

Gross Gas MMCF = IF(ABS(SUM(data[Gross Gas MMCF])) < 100,SUM(data[Gross Gas MMCF]),BLANK())

Gross NGL MBBL = IF(ABS(SUM(data[Gross NGL MBBL])) < 100,SUM(data[Gross NGL MBBL]),BLANK())

Gross Oil = IF(ABS(SUM(data[Gross Oil MBBL])) < 100,SUM(data[Gross Oil MBBL]),BLANK())

Net Gas MMCF = IF(ABS(SUM(data[Net_Gas_MMCF])) < 100,SUM(data[Net_Gas_MMCF]),BLANK())

Net MBOE = IF(ABS(SUM(data[NetMBOE])) < 100,SUM(data[NetMBOE]),BLANK())

Net NGL MBBL = IF(ABS(SUM(data[Net_NGL_MBBL])) < 100,SUM(data[Net_NGL_MBBL]),BLANK())

Net Oil MBBL = IF(ABS(SUM(data[Net_Oil_MBBL])) < 100,SUM(data[Net_Oil_MBBL]),BLANK())

I have attached the modified pbix file below, I hope this helps!

If the above does not solve the problem you are experiencing, please describe in more detail the problem you are experiencing and we will do our best to solve it for you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paulfromhouston]

Hi Fen,

Thanks for help! What I want to have is to exclude some smaller data out of the bar chart, for example, exclude those numbers between -200 and +200, then only those bigger (absolute) numbers stay, thus I can see what are the bigger changes on the bar chart. So ideally, I hope there is a filter (slicer) on the page that allows me to change it dynamically in order to get a smaller bar chart that fits my screen. This is the first solution that I am looking for. The second one is that if there is a filter/slicer that allows me to plot the top m columns and bottom n columns on the bar chart. For example, I want to only display the top 15 and bottom 10 bars, or top 20 and bottom 5, etc. I notice that the Bar chart has a function to exclude data. It needs to click on one bar or multiple bars (with Ctrl key), then  right click mouse, the pop menu will show "Exclude", which allows me exclude those selected data. But this is awarkward, plus, this doesn't tell audience my displayed data is filtered data. It will be ideal, I can have a box to specify the Top M and Bottom N number of bars being displayed. Hope this is clear, but let me if you need more explanation.

Thanks,

Paul

[Ashish_Mathur]

Hi,

I may be able to help you with the second filter.  Share the download link of the PBI file.

[paulfromhouston]

Ashish, Thank you so much for help. Attached is a sample dataset. https://github.com/paulbliu/test-dynamic-data-exclusion please download it from GitHub, let me know if you have any issues to download. Thanks again, Paul