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
dharmendars007's avatar
dharmendars007
Icon for Memorable Member rankMemorable Member
1 year ago

Hello Anonymous , 

Your current measure divides the filtered Value by the total Value, ignoring the Status filter. However, the ALL function is removing only the Status filter, and this might not behave as expected when combined with other filters

 

Please try the below measure..

_Measure =
VAR D = [Value]
VAR AD = CALCULATE(
[Value],
REMOVEFILTERS('FactTable'[Status]))
VAR RESULT = DIVIDE(D, AD, 0)
RETURN RESULT

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

 

Thank You

Dharmendar S

LinkedIN