How to ignore a specific filter in measure and keep all other filters?

[Mouadh_M]

Hello, 

I am currently facing a problem with eliminating only one specific filter from a measure and keep all other possible filters.

Below an example:

ID	Production_Line	Defect	Produced quantity (meter)	DateProd
511162122	EXT08	OK	8,494.00	18/04/2022
511162993	EXT01	Spark	8,463.00	18/04/2022
511162992	EXT01	Node	8,463.00	18/04/2022
511162128	EXT05	Spark	8,494.00	18/04/2022
511162121	EXT06	Spark	4,344.00	18/04/2022
511162126	EXT01	OK	8,494.00	18/04/2022
511162124	EXT02	OK	4,149.00	17/04/2022
511162127	EXT01	OK	8,494.00	17/04/2023
511162033	EXT01	Diameter	112.00	17/04/2024
511162034	EXT01	Sparl	93.00	17/04/2025
511162021	EXT07	OK	4,990.00	18/04/2022
511162023	EXT07	OK	4,989.00	16/04/2022
511162024	EXT07	OK	4,628.00	16/04/2023
511162020	EXT07	OK	4,989.00	16/04/2024
511162025	EXT08	Node	165.00	16/04/2025
511162035	EXT08	OK INC	1,122.00	16/04/2026
511162028	EXT02	OK INC	1,200.00	16/04/2027
511162022	EXT02	OK	4,990.00	18/04/2022
511162036	EXT01	Node	725.00	15/04/2022
511162032	EXT05	Node	3,045.00	15/04/2023
511162037	EXT01	Node	70.00	15/04/2024
511162031	EXT01	OK	3,507.00	15/04/2025
511162038	EXT01	OK	4,995.00	15/04/2026
511162039	EXT01	OK	4,989.00	18/04/2022
511162029	EXT01	Spark	168.00	18/04/2022

So, I want to apply this equation: sum(Production quantity for specefic selected defect) / sum(production quantity, taking into account all types of defects), also i want the equation to be succeptible to change each time i choose a specific defect on slicer.

However i want to keep all other possible filters applicable (for example: dateprod, and line).

Thank you in Advance 

[tackytechtom]

Hi @Mouadh_M ,

Were you thinking of something like this? 🙂

Here the DAX:

Measure = 
DIVIDE ( 
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ),
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table) )
)

Note, the measure is pretty dynamic and works for other dimensions, too. Like this:

This will lead to the following behaviour when dragging in another attribute:

If you would like to have it more fixed like this (see measure2)...

... I'd recommend the following DAX code instead:

Measure2 = 
VAR _a =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALLEXCEPT( Table, Table[Defect] ) )
VAR _b =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALL ( Table) )
RETURN
DIVIDE ( _a, _b )

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Mouadh_M]

Hello tomfox, 

I really appreciate your help, your suggestion was helpful, but i found that if i use the defect filter it gave me a better result: 

Measure = 
DIVIDE ( 
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ),
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table[Defect]) )
)

can you help me figure how to plot  automatically the top defect by measure? 

thank you in advance

[Anonymous · Accepted solution]

Hi @Mouadh_M ,

If you want to get toe top defect by measure, you can try to create a rank measure and then get defect whose rank =1.

Rank = RANKX(CALCULATETABLE( VALUES('Table'[Defect]),ALL('Table')),[Measure],,DESC,Dense)

Top Defect = 
CALCULATE(MAX('Table'[Defect]),FILTER(VALUES('Table'[Defect]),[Rank] = 1)) 

Result is as below.

By Default:

Select Production line:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[onursari2009]

Thank you for your kind contributions.