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.

     

     

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.

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

    Hi,

    Try this measure

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

    I have removed the criteria of Flag in the filter pane.

    Hope this helps.

     

    • 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]))

       

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

        Good to know that.  You may write multiple GENERATE() functions but that would be cumbersome.  Someone else will help you with a better solution.