Aggregating values based on slicer selection

[ant_lah]

Hello,

Been banging my head on an issue for a while now, and I'm new enough that I struggle to put it concisely (and hence search effectively).

I am trying to build a dashboard where a user can select multiple agriculture-related datapoints, input cut-off ranges, and see a map of states/districts in India that satisfy the chosen conditions. The simplest way to do that would be to have a dataset with 1 row per district, and one column per data point (e.g., number of wholly owned farms). But that radically flattens the origin data, where the number of farms owned is broken down by the size of farms:

State	District	Farm size	number of farms owned
state 1	district 1	small	30
state 1	district 1	medium	50
state 1	district 1	large	20
state 1	district 2	small	11
state 1	district 2	medium	24
state 1	district 2	large	70

(in reality, I have 5 size categories and 8 datapoints of interest for just this part of the data).

What I want to get, based on slicer selection:

When "small" and "medium" are selected, I need the new column to sum just the small and medium farm numbers:

State	District	Farm size	number of farms owned	Sum by size
state 1	district 1	small	30	80
state 1	district 1	medium	50	80
state 1	district 1	large	20	80
state 1	district 2	small	11	35
state 1	district 2	medium	24	35
state 1	district 2	large	70	35

When large is selected, I'll need just the large values summed:

State	District	Farm size	number of farms owned	Sum by size
state 1	district 1	small	30	20
state 1	district 1	medium	50	20
state 1	district 1	large	20	20
state 1	district 2	small	11	70
state 1	district 2	medium	24	70
state 1	district 2	large	70	70

If I use the 'number of farms owned' column directly, then a slicer that filters values in the 'number of famrs owned' columns based on farm size works fine with a single selection, but on a multiple selection it will use one of the separate values rather than combining the values. I know how to do a groupby/summarize statement that will get me a sum per district over all farm sizes, but I have not been able to make that responsive to a slicer selection of farm sizes: I want to be able to specify, say, small + medium farm sizes and get a per-district sum of just those.

The closest I've got is a measure

number owned sum selected categories = 
CALCULATE(
    SUM(C_Farms[number owned]),
    ALLSELECTED(C_Farms[O_Farms_Farm size categories]))

That does what I want to get within a table visual, but I can't drop it onto a slicer, and if I create a column in the table with the same statement, it stops responding to the slicer selection.

Is there a way to do this?

Link to my dashboard:

shared.pbix

Thank you all so much!

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[ant_lah]

Good point--edited the original post with examples. Thanks!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ant_lah]

Hi,

The measure does exactly what I want within the table visual (as does the measure I listed in the post), but I can't find a way to use it outside the table visual--specifically, I need to use the dynamically calculated number of farms owned in a "between" slicer that will control which districts are higlighted on a filled map (that is, districts where the sum of farms owned falls between the minimum-maximum numbers specified in the slicer).

If I understand correctly, to make that work, the dynamic sum needs to be a table column rather than a measure--or can a measure be used independently in a slicer?

Thank you so much for your help!

Anton

[Ashish_Mathur]

You are welcome.  It loks like your ultimate objective is to create a slicer of the "Sum by size" column.  Do you want the slicer to have individual figures or buckets i.e. 0-20, 21-40 etc?  If you want buckets, then please specify what those buckets should be?

[ant_lah]

Hi Ashish Mathur,

I am looking for a slicer on individual figures, pretty much what I have on the screenshot below, but with the option to specify which size category farms should be considered for selection.

Thanks again,

Anton

[danextian]

Hi @ant_lah 

Try the following:

farms by district and state = 
CALCULATE (
    SUM ( 'Table'[number of farms owned] ),
    ALLEXCEPT ( 'Table', 'Table'[District], 'Table'[state] )
)

[ant_lah]

Hi @danextian ,

Your proposal correctly sums the values over state and district, but it sums all of the size categories regardless of which of them I have selected in a slicer. Am I missing something?

Thanks!

Anton