Forum Discussion

JadeDoherty's avatar
JadeDoherty
Regular Visitor
5 years ago
Solved

Need assistance with DAX Measure

Hello - wondering if someone might be able to assist with a DAX Measure / Calculation.   Basically the sales team is looking to run a Sales promotion that will be based on a points system - where p...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is a measure expression that gets your desired results in a table visual with the SalesRep column.

     

    Promotion Points =
    SUMX (
        VALUES ( SalesData[Customer] ),
        VAR hammers =
            CALCULATE (
                COUNT ( SalesData[Date] ),
                SalesData[Team] <> "Wholesale",
                SalesData[Product] = "Hammer"
            )
        VAR bolts =
            CALCULATE (
                COUNT ( SalesData[Date] ),
                SalesData[Team] <> "Wholesale",
                SalesData[Product] = "Bolt"
            )
        VAR hammerpoints =
            IF (
                hammers > 3,
                3,
                hammers
            )
        VAR boltpoints =
            IF (
                bolts * 2 > 10,
                10,
                bolts * 2
            )
        RETURN
            hammerpoints + boltpoints
    )

     

     

    Regards,

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Here is a measure expression that gets your desired results in a table visual with the SalesRep column.

 

Promotion Points =
SUMX (
    VALUES ( SalesData[Customer] ),
    VAR hammers =
        CALCULATE (
            COUNT ( SalesData[Date] ),
            SalesData[Team] <> "Wholesale",
            SalesData[Product] = "Hammer"
        )
    VAR bolts =
        CALCULATE (
            COUNT ( SalesData[Date] ),
            SalesData[Team] <> "Wholesale",
            SalesData[Product] = "Bolt"
        )
    VAR hammerpoints =
        IF (
            hammers > 3,
            3,
            hammers
        )
    VAR boltpoints =
        IF (
            bolts * 2 > 10,
            10,
            bolts * 2
        )
    RETURN
        hammerpoints + boltpoints
)

 

 

Regards,

Pat

 

  • JadeDoherty's avatar
    JadeDoherty
    Regular Visitor
    5 years ago

    Thank you very much mahoneypat !

     

    That looks to do the trick, and appears simple enough that I can even understand it... 🙂  You my friend are a rockstar!