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
How to create a stacked bar containing top 5 sales and others?
Hi All, I'm new to Power BI. I have a stacked graph like this below, The visual shows the total sales across different countries and the cities acts like stacks. My requirement is to regroup this stack into two categories - Top 5 city sales as one group, and the rest as a separate group. Example- US consists of cities like NY, LA and many others. I need to group the top 5 cities in the US such as NY, LA and 3 other cities in one group, the other cities that do not fall in the top 5 should be in a separate group named "Others" (Like the below chart). The report should show data for all the countries like this. I have attached the link to the PBIX for your reference. Thanks to you in advance! PBIX file link - Download PBIX file here With regards, Shriram
