remove filters from the total

[CLCastroEn]

Hello!

I'm making a 100% stacked column chart. which is like this:

where the value field is given by the store count and legends are the state of the stores. Example: 81.6% do room count with stock, 9.05% count rooms with average stock, and so on. This chart serves me because it takes as the total all the room count that I have and performs the percentage of each of them. the problem arises when I use a filter and want to see only the low rooms:

it's this way, it takes about 100% just the filtered rooms. I need you to show how much the low rooms weigh, but with respect to the total. (should appear there about 5%)

I know there is a function called calculate, but it won't be used well. I appreciate tips!

[v-kelly-msft · Accepted solution]

Hello @CLCastroEn ,

Use "Stacked Column Chart" instead.

First, create a dimension table as shown below:

Table 2 = VALUES('Table'[Room])

Next, create a measure as follows:

Measure = 
var _selected=CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Room] in FILTERS('Table 2'[Room])&&'Table'[Room]=MAX('Table'[Room])))
var _total=CALCULATE(SUM('Table'[value]),ALL('Table'))
Return
IF(ISFILTERED('Table 2'[Room])=BLANK(),DIVIDE(SUMX(FILTER(ALL('Table'),'Table'[Room]=MAX('Table'[Room])),'Table'[value]),SUMX(ALL('Table'),'Table'[value])),IF(MAX('Table'[Room]) in FILTERS('Table 2'[Room]),DIVIDE(_selected,_total),BLANK()))

And you'll see:

For the related .pbix file, see attachment pls.

Saludos
Kelly

Have I answered your question? Mark my position as a solution!

[amitchandak]

@CLCastroEn , You have to create meausres like this. this will not lose gt with filters

divide([measure], calculate([measure], all(Table)))

[CLCastroEn]

@amitchandak prob+e doing this

Valor = DIVIDE(COUNTA(Base[Store Name]), CALCULATE(COUNTA(Base[Store Nbr]), ALL(Base)))

and when I do the chart they don't give me the right values. Could you help me?

[CLCastroEn]

@amitchandak

and how can I do room counting with a function?

so that it stays

divide([room count count], calculate([room count], all(Table)))