Filter problems

[Mary78]

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 fact table
Value = Sum('FactTable[Values])

_Measure =

VAR D = [Value]

VAR AD =

    CALCULATE (

        D,

        ALL ( 'FactTable'[Status] )

    )

VAR RESULT =

    DIVIDE ( D, AD, BLANK () )

RETURN

    RESULT

[Mary78]

So this is a simplifyed version of my fact table:

date_id		warehoue_id	Status	Days
20240101		1	Sent	1
20240102		1	Sent	1
20240101		2	packed	1
20240102		2	Sent	1
20240101		1	Sent	1
20240102		1	Sent	1
20240101		2	packed	1
20240102		2	Sent	1
20240101		1	Sent	1
20240102		1	Sent	1

My goal is to have a measure that show days on a status vs days on all statuses in selected filter context.

So with this filtering
date = 20240101
Status = Sent 
Measure should give 60% ( 3 days on packed & 2 days on Sent = 5 days. 3/5= 0.6 60%)

Or with filter:
Warehouse = 2
Status = Packed
Measure should give 50% ( 2 days on packed & 2 days on Sent = 4 days. 2/4= 0.5 50%)

My problem is to get measure to ignore filter on status and return all days for date & warehouse filter context.

[Mary78]

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]

Hi @Mary78 ,

Happy it works.

[v-fenling-msft]

Hi, @Mary78 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[dharmendars007]

Hello @Mary78 , 

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 

[anmolmalviya05]

Hi, please try below measure:
_Percentage =
VAR CurrentValue = SUM('FactTable'[Value]) -- Sum the current values
VAR TotalValue =
CALCULATE(
SUM('FactTable'[Value]),
REMOVEFILTERS('FactTable'[Status]) -- Remove filters on the 'Status' column
)
RETURN
DIVIDE(CurrentValue, TotalValue, BLANK())

[Bibiano_Geraldo]

Hi @Mary78 ,
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

[Mary78]

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 · Accepted solution]

Hi @Mary78 ,

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

[ribisht17]

hi @Mary78 

Please try ALLSELECTED there

Regards,

Ritesh

✔️ Please mark the answer if helpful so that it can help others as well😊