Calculating a dynamic 100% stacked visual denominator with multiple user defined variables
I am ultimately trying to come up with a measure that will allow me to plot a 100% stacked ribbon chart, and I'm getting hung up on trying to create the denominator for my measure Scenario: I have a year Slicer that allows multiple selections from the Table 'Year', column named [Year] I have a location slicer that allows muliple selections from the table 'Location', column named [Location Name] I have a table of data named 'Table1' that contains the following columns:[Year],[Location], [Type], & [Count] I want to calculate the percentage of a location type's count of that year's total. I also want the yearly total to adjust if the location slicer is used to filter out unwanted locations. Filtering by type is not allowed, only year and location filtering are allowed. An example of my dataset: Table1 Year Location Type Count 2020 A Red 5 2020 A Orange 6 2020 A Black 9 2020 B Orange 8 2020 B Black 5 2020 C Red 5 2020 C Orange 6 2020 C Black 33 2020 C Silver 2 2020 C Green 5 2020 D Green 4 2020 D Silver 14 2021 A Silver 6 2021 A Red 18 2021 B Orange 2 2021 B Black 15 2021 B Green 1 2021 C Silver 2 2021 D Silver 20 2021 D Green 11 2021 D Orange 15 2022 A Orange 4 2022 A Red 6 2022 A Black 9 2022 A Silver 8 2022 A Green 8 2022 B Red 9 2022 D Red 8 2022 D Silver 7 2022 D Green 15 Desired Outcomes: No Filtering: Filter on locations A & D Filter on Years 2020 & 2022 Filter on Years 2020 & 2022 & Locations A & D Any help would be appreciated. I've been trying a calculate function but can't seem to nail down the proper filters. I either end up a denominator equal to the numerator so every type has a value of 100% or I end up with the denominator equal to the yearly total with no location filtering, so the values don't add up to 100% when a location is selected, but looks correct when no filtering applied
