Forum Discussion
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
Hi Ryan_Michalski ,
I have created a simple sample, please refer to it to see if it helps you.
Create measures.
selectedvalue = SELECTEDVALUE('Table'[Location])total color = CALCULATE ( SUM ( 'Table'[Count] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Location] = [selectedvalue] && 'Table'[year] = SELECTEDVALUE ( 'Table'[year] ) && 'Table'[Type] = SELECTEDVALUE ( 'Table'[Type] ) ) )total value = CALCULATE ( SUM ( 'Table'[Count] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Location] = [selectedvalue] && 'Table'[year] = SELECTEDVALUE ( 'Table'[year] ) ) )% = [total color]/[total value]Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Ryan_Michalski , If Table and Table one are connected then You need measure like
divide(sum(Table1[Count]), calculate(Table1[Count]), filter(allselected(Table1) , Table1[Year] = Max(Table1[Year]) ) )
If the are not connected
m1 = calculate(Sum(Table1[Count]), Filter(Table1, Table1[Year] in Values(Table[Year]) && Table1[Location] in Values(Table[Location]) ) )
Then create a measure
divide([m1], calculate([M1], filter(allselected(Table1) , Table1[Year] = Max(Table1[Year]) ) )
Hi Ryan_Michalski ,
I have created a simple sample, please refer to it to see if it helps you.
Create measures.
selectedvalue = SELECTEDVALUE('Table'[Location])total color = CALCULATE ( SUM ( 'Table'[Count] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Location] = [selectedvalue] && 'Table'[year] = SELECTEDVALUE ( 'Table'[year] ) && 'Table'[Type] = SELECTEDVALUE ( 'Table'[Type] ) ) )total value = CALCULATE ( SUM ( 'Table'[Count] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Location] = [selectedvalue] && 'Table'[year] = SELECTEDVALUE ( 'Table'[year] ) ) )% = [total color]/[total value]Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Polly,
This worked. Thanks so much for the help!!
- wdx223_Daniel
Community Champion
no filters
filter A&D
I tried your example, and it doesn't work with multiple locations selected. I used the data in my original message. In this test, when I select 2 locations for a year, I see a percentage that adds up to 200%. location 1 has a percenage that adds up to 100% and location 2 has a percentage that adds up to 100%. Instead, what I'm looking for, is to see the totals for each type divided by the total counts for all selected locations.
In the below screenshot, I created measures using the names in your screenshot. I would want "Total" to equal the total for the type, so types 'Orange' & 'Black' should show a total of 14, and the "Yearly Total" for all rows should be the total for all selected locations, so the yearly total should be 33.
With this example, I would expect the resulting table to be:
Year Location Type Count Example 1 Total Example 1 Yearly Total Example 1 % 2020 A Black 9 14 33 42% 2020 A Orange 6 14 33 42% 2020 A Red 5 5 33 15% 2020 B Black 5 14 33 42% 2020 B Orange 8 14 33 42%
