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.

     

     

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

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

    Thanks for the response. M2 should be 450 aggregation should happen on top of Col2. Results are not matching with screen shot.

    Summary Table(Filter -> Exclude -> Col4-Z1)
  • MarshalSK's avatar
    MarshalSK
    Icon for Resolver I rankResolver I
    2 years ago

    Hi Ahmedx , Thanks for the response and sorry expected result is slightly different which i missed out.

    Sum of Sales should be based on Col1+Col2 (Expcted output as below screen shot)

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      Hi,

      Share the download link of the PBI file.

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

        Hi Ashish_Mathur  my screen shots from excel and Ahmedx  pbi file is pretty close, you can use the same . Thanks.

         

        also let me know if anything specific you need to help me.