Forum Discussion

laciodrom_80's avatar
laciodrom_80
Icon for Helper IV rankHelper IV
5 years ago
Solved

Hint about DAX calculation

Hi all,

I've got a table with a string column Customer, a number column Revision and a number column Price: I wish to sum all values in Price column but only considering the max revision value for the same customer columns values.

 

 

CUSTOMER       REVISION     PRICE
CustomerA          100        2
CustomerB          80         4
CustomerB          81         6
CustomerC          10         5
CustomerD          14         2
CustomerD          15         3

 

 

I'd like to obtain: 2+6+5+3 = 16

What DAX expression should I use?

 

Thanks a lot for any clue!

Need Help
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi laciodrom_80 , 

     

    Would you please use the following measure ?

     

    Measure = SUMX(ADDCOLUMNS(SUMMARIZE('Table','Table'[CUSTOMER],"maxrevision",MAX('Table'[REVISION])),"_price",CALCULATE(MAX('Table'[Price]))),[_price])

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    laciodrom_80 , Try like

     

    sumx(values(Table[CUSTOMER]) ,calculate(max(Table[price])))

     

    or

    sumx(Summarize(Table,Table[CUSTOMER],"_1" ,calculate(max(Table[price]))),[_1])

    or

    sumx(values(Table[CUSTOMER]) ,(max(Table[price])))

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        laciodrom_80 , Try like

        sumx(values(Table[CUSTOMER]) ,lastnonblankvalue(table[revision ],max(Table[price])))

         

        In visual  take max or last of revision, if needed