I have two lists of keywords: Cause and Effect. I have filters allowing for multiple selection out of both list (incl. select all).
I need to find the (selected) keywords from (both) these lists in a Table column Text and sum up the amounts Value corresponding to each line that contains the selection (in the sense of an AND).
I also need to show the sums in a chart with Date only (respectively a Calendar out of it) and just this calculated sum.
Here some mock data:
| Cause | Effect |
|---|---|
| apple | allergy |
| peanut | recall |
| raisin |
Respectively for Table:
| Text | Date | Value |
|---|---|---|
| Apples’ China risk | 9/8/2023 | 778,985 |
| Pick Peanuts, Pick Apples and Get Out of Town | 10/1/2023 | 13,270,269 |
| A Low-Cost Grocery Delivery Service With Much More Than Ugly Apples | 11/9/2023 | 5,543,076 |
| Krispy Kreme urgently recalls four-pack of doughnuts over peanut allergy fears | 8/7/2023 | 4,013,349 |
| Recall over allergy fears: Chocolate raisin snacks may contain peanuts | 10/9/2023 | 6,518,508 |
| I have a severe allergy to strawberries | 9/23/2023 | 4,013,349 |
| Aldi urgently recalls deli meats over allergy fears | 8/23/2023 | 4,013,349 |
E.g. if raisin, allergy and recall are all selected, I should see only 6,518,508. If only peanut is is selected, then 13,270,269 + 4,013,349 + 6,518,508 = 23,802,126
On a chart by Year, Month, Day, for 10/1/2023 one should only see 13,270,269. And aggregated over the whole Oct then 19,788,777.
I’m not able to do this, whatever calculated table, filters or whatever else I use. Once Text is missing from the visual (as is of course the case for the chart), it shows me wrong dates and values and the chart won’t work at all.
Could anyone please help? At least give me a hint of the logic of the whole stuff. Thanks a lot!
