Forum Discussion

CLCastroEn's avatar
CLCastroEn
Icon for Helper IV rankHelper IV
6 years ago
Solved

remove filters from the total

Hello!

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

CLCastroEn_0-1602861877728.png

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:

CLCastroEn_1-1602862016190.png

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's avatar
    v-kelly-msft
    6 years ago

    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:

    Screenshot 2020-10-21 140217.pngScreenshot 2020-10-21 140249.png

    For the related .pbix file, see attachment pls.

    Saludos
    Kelly

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

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

    • CLCastroEn's avatar
      CLCastroEn
      Icon for Helper IV rankHelper IV
      6 years ago

      @amitchandak

      and how can I do room counting with a function?

      so that it stays

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

    • CLCastroEn's avatar
      CLCastroEn
      Icon for Helper IV rankHelper IV
      6 years ago

      @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?
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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:

    Screenshot 2020-10-21 140217.pngScreenshot 2020-10-21 140249.png

    For the related .pbix file, see attachment pls.

    Saludos
    Kelly

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