Forum Discussion

paulfromhouston's avatar
paulfromhouston
Frequent Visitor
1 year ago
Solved

How to dynamically exclude data from bar chart

    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 ...
Need Help
  • paulfromhouston's avatar
    paulfromhouston
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    paulfromhouston
    Frequent Visitor
    1 year ago

    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