Forum Discussion
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 fact table
Value = Sum('FactTable[Values])
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 RESULTIf 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
11 Replies
hi Anonymous
Please try ALLSELECTED there
Regards,
Ritesh
✔️ Please mark the answer if helpful so that it can help others as well😊
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 RESULTThanks 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?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 RESULTIf 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
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())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 filtersPlease try the below measure..
_Measure =
VAR D = [Value]
VAR AD = CALCULATE(
[Value],
REMOVEFILTERS('FactTable'[Status]))
VAR RESULT = DIVIDE(D, AD, 0)
RETURN RESULTIf 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
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.
Hi, Anonymous
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.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.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 🙏Hi Anonymous ,
Happy it works.
