Forum Discussion

doppietta's avatar
doppietta
Frequent Visitor
8 years ago

selective slicer

Hi all,   I need to plot on clustered column chart the range distribution twice: the first one is the unfiltered version (made of by all the records in the table) and I need the second one to be dy...
Need Help
doppietta's avatar
doppietta
Frequent Visitor
8 years ago

Hi Tobias,

 

I had to change something in the formula but it seems to work now. The measure I used is:

 

MyMeasure = CALCULATE(COUNT(HLCO_FREQ_DISTRIBUTION_1[D_RGHL]),ALLEXCEPT(HLCO_FREQ_DISTRIBUTION_1, HLCO_FREQ_DISTRIBUTION_1[RGHL_CUSTOM_RANGE2]))

 

Unfortunately I have another issue: if narrowing down the span of the aqua series I hit a period for whom that series has no values in a range, that range isn't showed (even if the other series has values in that range).

Maybe it should be more clear in the following images:

 

In this image all the ranges I defined are visible because both series have at least one element in each range.

 

In this image I narrowed down the slicer and the latest range (>200) disappeared even if the charcoal series has values in that range.

 

In any case thank you very much Tobias.

Regards,

Alberto

Anonymous's avatar
Anonymous
Not applicable
8 years ago

In the chart customisation pane, if you right-click the field that is used by the aqua series, you should see an option 'Show items with no data'.

 

Ticking this option will always show all of your category buckets, even when there are no values in the filtered view.

 

(P.S. Sorry I missed the fact that needed a 'Count' rather than a 'Sum' from your original post).

  • doppietta's avatar
    doppietta
    Frequent Visitor
    8 years ago

    Hi Tobias,

     

    I think my nightmare is over now ;-)

     

    I had to modify practically everything because 'Show items with no data' doesn't work if using 'Percent of Grand Total' (it shows a warning on the graph saying that it's unsupported).
    So I removed the percentage view just for trying...the warning disappeared but still no luck. To make that work I had to move the custom ranges I defined in an external table and create 1:N relationship between the table of ranges and the table of data.
    Then I cloned the data table to provide the data for the filtered data-series, binding the daytime column to the slicer.
    Finally I had to define a custom measure for each data-series to calculate the percentage distribution:

     

    MyMeasure = CALCULATE(COUNT(RAW_DATA[D_RGHL]))/(CALCULATE(COUNT(RAW_DATA[D_RGHL]),ALLSELECTED())/100)

     

    It's working now; I'm pretty sure there is a simpler way to do that...but I didn't found it (yet).

     

    Again, thank you very much.
    Regards,
    Alberto