Forum Discussion

riadorra's avatar
riadorra
Icon for Helper II rankHelper II
8 years ago
Solved

create new SUMIF colum

Hi,

I have 2 tables, one containing the products ID and other cotaining Sales (Values and Quantity sold) per product ...

On the register table I want to create a new colum with the average sales price price ...

 

Could anyone help me to solve this SUMIF problem?

 

thank you.

Riad

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    riadorra,

     

    You may check the following DAX.

    Column =
VAR t =
    FILTER ( Table2, Table2[productID] = Table1[productID] )
RETURN
    DIVIDE (
        SUMX ( t, Table2[Quantity] * Table2[Value] ),
        SUMX ( t, Table2[Quantity] )
    )

3 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I'm not sure what this has to do with SUMIF or why you couldn't just create a measure dividing the revenue by the quantity?

    • riadorra's avatar
      riadorra
      Icon for Helper II rankHelper II
      8 years ago

      Hi,

       

      based on this new colum, i 'll classificate the products on pre determined ranges following the ranges bellow:

       

      1. sales price from $0 to $50

      2. sales price from $51 to $100

      3. sales price from $101 to $150

      4. sales price from $151 to $200

       

      thank you.

      Riad 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    riadorra,

     

    You may check the following DAX.

    Column =
VAR t =
    FILTER ( Table2, Table2[productID] = Table1[productID] )
RETURN
    DIVIDE (
        SUMX ( t, Table2[Quantity] * Table2[Value] ),
        SUMX ( t, Table2[Quantity] )
    )