Measure (based on 2 other measures) to ignore slicer 1 but include slicer 2?

[Anonymous]

Dear all,

I have the following.

DAX Measure 1: Valves Open Orders = CALCULATE(COUNTROWS(FactTable_Orders), FILTER(FactTable_Orders, FactTable_Orders[ProductType] = "Valves"), FILTER(FactTable_Order, FactTable_Orders[Status] = "Open"))

DAX Measure 2: Open Orders = CALCULATE(COUNTROWS(FactTable_Orders), FILTER(FactTable_Orders, FactTable_Orders[Status] = "Open"))

DAX Measure 3: Valves % of Open Orders = DIVIDE (Valves Open Orders, Open Orders) *100

On the report page, I have a slicer for Regions e.g. North, South, East, West and for Month e.g. Jan, Feb, Mar, etc.

How do I modify Measure 3 to show Valves % of Total Open Orders when using the slicer? That is when I use the slice to "North" I would like Measure 3 to give me Valves Open Orders for North Region divided by the Total Open Orders instead of North Open Orders while taking into consideration the Month slicer?

Can someone point me to the right direction?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure 3 to archive your requirement:

Valves % of Open Orders =
DIVIDE (
    [Valves Open Orders],
    COUNTROWS (
        FILTER (
            ALLEXCEPT ( 'FactTable_Orders', 'FactTable_Orders'[Regions] ),
            'FactTable_Orders'[Status] = "Open"
        )
    )
) * 100

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymous  Can you please try the following measures before you do DIVIDE to see if they behave to your liking and yet produces a subset of the data based on the slicer values selected

Valves Open Orders:=
CALCULATE (
    COUNTROWS ( FactTable_Orders ),
    KEEPFILTERS (
        FILTER (
            ALL ( FactTable_Orders[ProductType], FactTable_Orders[Status] ),
            AND (
                FactTable_Orders[ProductType] = "Valves",
                FactTable_Orders[Status] = "Open"
            )
        )
    )
)

Open Orders:=
CALCULATE (
    COUNTROWS ( FactTable_Orders ),
    KEEPFILTERS (
        FILTER ( ALL ( FactTable_Orders[Status] ), FactTable_Orders[Status] = "Open" )
    )
)