MS - Conditional Total

[mshaikh]

Hi

I am looking for conditional sales total formula using following condition. Please find attached document for reference.

Complete_Date <= filter date  Filter date is between 2018-03-25 and 2018-03-31

Complete Percent = 100

Salesrep between SP1 and SP2

Code = Sales

Expected result = 2800

Can someone please provide suitable formula which work even after changing filter.

Thanks in advance,

MS

[Anonymous]

HI @mshaikh,

You can try to use below measure if it works for your requirement:

Measure =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER (
        ALLSELECTED ( Table ),
        [Contract_Complete Date] <= MIN ( Calendar[Date] )
            && [Complete_Percent] = 100
            && [Sales Person] IN ALLSELECTED ( Table[Sales Person] )
            && [Code] IN ALLSELECTED ( Table[Code] )
    )
)

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[mshaikh]

Hi

Thanks for the formula.  Salesperson IN ALLSELECTED is not working with slicer. However when we manully add Salesperson code in measure it works. Please have a look at attached result set for reference.

[mshaikh]

Hi

Thanks for the formula.  Salesperson IN ALLSELECTED is not working with slicer. However when we manully add Salesperson code in measure it works. Please have a look at attached result set for reference.                Thanks,  MS

[Anonymous · Accepted solution]

HI @mshaikh,

When you use ALLSELECTED function in filter who already used ALL function, it will ignore filter effect which allselected provide.

For this scenario, please use VAR function to define selected value list outside of filter which used ALL function.

Measure =
VAR _SPerson =
    ALLSELECTED ( Table[Sales Person] )
VAR _SCode =
    ALLSELECTED ( Table[Code] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALL ( Table ),
            [Contract_Complete Date] <= MIN ( Calendar[Date] )
                && [Complete_Percent] = 100
                && [Sales Person] IN _SPerson
                && [Code] IN _SCode
        )
    )

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

Regards,

Xiaoxin Sheng