Combine Data columns based on filters/slicer

[rnanban]

Hi all,

Apologies for the newbie question I am new to PowerBI.

Example of what I am trying to achieve:

Example Data set (Dates not shown in visual):

|Site ID|Site Name|Food Category|Sales|Date|
|1|Test Site 1|1|$40|June 23|
|1|Test Site 1|2|$50|June 23|
|1|Test Site 1|1|$50|May 23|
|1|Test Site 1|2|$60|May 23|
|2|Test Site 2|1|$100|June 23|
|2|Test Site 2|2|$200|June 23|
|2|Test Site 2|1|$100|May 23|
|2|Test Site 2|2|$50|May 23|

When filtered by "May 23" & "June 23" it would combine all the "Sales" for the same "Food Category"

|Site ID|Site Name|Food Category|Sales|
|1|Test Site 1|1|$90|
|1|Test Site 1|2|$110|
|2|Test Site 2|1|$200|
|2|Test Site 2|2|$150|

When unselecting "May 23" will only show "June 23" and vise versa and when unselecting every date range it will show all combined.

I hope this makes sense.

Thank you

[dk_dk · Accepted solution]

Hi @rnanban 

What you describe is how the default behaviour should work.

If you have a table visual in your report that contains Site ID, Site Name, Food Category and Sales (NOT the Date)

And a filter or slicer on the page that contains the Date, then by interacting with the slicer/filter you should get the behavior that you are looking for.

Note that you would need to make sure that the Sales column has the right data type (number/currency, NOT text for example)

Let me know if it is still not working as intended. 🙂

Best regards,
Daniel