Calculating a dynamic 100% stacked visual denominator with multiple user defined variables

[Ryan_Michalski]

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

[wdx223_Daniel]

no filters

filter A&D

[Ryan_Michalski]

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%

[v-rongtiep-msft · Accepted solution]

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.

[Ryan_Michalski]

Polly,

  This worked.  Thanks so much for the help!!

[amitchandak]

@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]) ) )