Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ryan_Michalski
Frequent Visitor

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:

Ryan_Michalski_4-1667600226710.png

 

 

Filter on locations A & D

Ryan_Michalski_5-1667600238174.png

 

 

Filter on Years 2020 & 2022

Ryan_Michalski_6-1667600259839.png

 

 

Filter on Years 2020 & 2022 & Locations A & D

Ryan_Michalski_7-1667600270580.png

 

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]

 

vpollymsft_0-1667790402959.pngvpollymsft_1-1667790416157.png

vpollymsft_2-1667790427579.png

 

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.

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

no filters

wdx223_Daniel_0-1667796405346.png

filter A&D

wdx223_Daniel_1-1667796447714.pngwdx223_Daniel_2-1667796479718.png

wdx223_Daniel_3-1667796504011.png

wdx223_Daniel_4-1667796624402.png

 

 

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:

YearLocationTypeCountExample 1 TotalExample 1 Yearly TotalExample 1 %
2020ABlack9143342%
2020AOrange6143342%
2020ARed553315%
2020BBlack5143342%
2020BOrange8143342%

 

 

Example 1.jpg

Anonymous
Not applicable

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]

 

vpollymsft_0-1667790402959.pngvpollymsft_1-1667790416157.png

vpollymsft_2-1667790427579.png

 

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!!

amitchandak
Super User
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]) ) )

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.