Forum Discussion
Aggregating values based on slicer selection
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:
Thank you all so much!
8 Replies
Hi,
Based on the table that you have shared, show the expected result very clearly.
Good point--edited the original post with examples. Thanks!
Hi,
PBI file attached.
Hope this helps.
