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

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. 

    • 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.