Forum Discussion

MarshalSK's avatar
MarshalSK
Icon for Resolver I rankResolver I
2 years ago
Solved

Need a assistance with DAX calculation

Hi Folks, Need your experties, for DAX formula to get Sum of sales based on Col2.   I have provided Data, screen shot in Expected result (without filter and with Filter )   Sample Data: Month...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MarshalSK ,

     

    I suggest you to try code as below to create a measure.

    Sum of Sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[MonthYear] = MAX ( 'Table'[MonthYear] )
            && 'Table'[Col1] = MAX ( 'Table'[Col1] )
            && 'Table'[Col2] = MAX ( 'Table'[Col2] )
    )
)

    Result is as below.

    Without Filter

    Filter -> Exclude ->Col4-Z1

    Filter -> Exclude ->Col4-Y1,Col3-B4

     

    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.

     

     

MarshalSK's avatar
MarshalSK
Icon for Resolver I rankResolver I
2 years ago

Ashish_Mathur . really helpful. how to include two more columns in m3 ?

m3 = CALCULATE([m1], ALLSELECTED(),GENERATE(VALUES('Table'[Col1]),VALUES('Table'[Col2])))
GENERATE(VALUES('Table'[Col1]),VALUES('Table'[Col2]))

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MarshalSK ,

 

I suggest you to try code as below to create a measure.

Sum of Sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[MonthYear] = MAX ( 'Table'[MonthYear] )
            && 'Table'[Col1] = MAX ( 'Table'[Col1] )
            && 'Table'[Col2] = MAX ( 'Table'[Col2] )
    )
)

Result is as below.

Without Filter

Filter -> Exclude ->Col4-Z1

Filter -> Exclude ->Col4-Y1,Col3-B4

 

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.