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
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Ok I got it to work, looks like this

_TEST =
VAR D = [Days]
VAR AD =
CALCULATE (
[Days],
ALLEXCEPT ( 'factTable', 'factTable'[Warehouse_id], 'Date'[Date_id] )
)
VAR RESULT =
DIVIDE ( D, AD, BLANK () )
RETURN
RESULT

If I replace [Days] in VAR AD with D measures stops giving me all rows for Status.

Any ways it works, big thanks for all help and input 🙏

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi Anonymous ,

Happy it works.