Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Filter problems

My _Measure is not delivering what I expected. I need a % for value on Status vs total Supid measure give 100% when filterd using Status Any Ideas? Status and Values are columns in the same ...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi Anonymous ,

    Try this measure:

    _Measure =
VAR D = [Value]  -- The current value based on filters
VAR AD =
    CALCULATE(
        [Value],
        ALLEXCEPT('FactTable', 'FactTable'[Other Columns...])  -- Preserve filters on other columns except Status
    )
VAR RESULT =
    DIVIDE(D, AD, BLANK())  -- Calculate the percentage
RETURN
    RESULT

     

    If the above dont work, please consider to try this one with SUMX:

    _Measure =
VAR D = [Value]
VAR AD =
    SUMX(
        ALL('FactTable'[Status]),
        CALCULATE(SUM('FactTable'[Values]))
    )
VAR RESULT =
    DIVIDE(D, AD, BLANK())
RETURN
    RESULT
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi Anonymous ,
Looking at your current approach, the issue likely arises because you're using the ALL function, which removes any filters on the Status column, causing the result to always be based on the total sum of values across all statuses, even when the table is filtered.

 

Please try this updated version of your measure:

 

_Measure =
VAR D = [Value]  -- The sum of 'FactTable[Values]' in the current context
VAR AD = 
    CALCULATE (
        [Value],  -- The total sum of values across the entire FactTable (not just filtered by Status)
        REMOVEFILTERS('FactTable'[Status])  -- This removes only the filter on the 'Status' column
    )
VAR RESULT =
    DIVIDE(D, AD, BLANK())  -- Dividing the current value by the total value to get the percentage
RETURN
    RESULT

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks all you guys for the replys,  unfortunatly non of them have solve my issue.

    Then thing is that this format for a % measure work fine when column i don´t want filterd are in a dim tabel and not in the fact were the value is.

    So maby issue is that value and column I want to removefilter on are in the same table? 

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      Hi Anonymous ,

      Try this measure:

      _Measure =
VAR D = [Value]  -- The current value based on filters
VAR AD =
    CALCULATE(
        [Value],
        ALLEXCEPT('FactTable', 'FactTable'[Other Columns...])  -- Preserve filters on other columns except Status
    )
VAR RESULT =
    DIVIDE(D, AD, BLANK())  -- Calculate the percentage
RETURN
    RESULT

       

      If the above dont work, please consider to try this one with SUMX:

      _Measure =
VAR D = [Value]
VAR AD =
    SUMX(
        ALL('FactTable'[Status]),
        CALCULATE(SUM('FactTable'[Values]))
    )
VAR RESULT =
    DIVIDE(D, AD, BLANK())
RETURN
    RESULT