Forum Discussion

jorge_mejia's avatar
jorge_mejia
New Member
9 years ago
Solved

Manipulating Measures by Different Cuts

Hello Power BI Community,

 

 

I am trying to calculate the Average Sales Price (ASP) for each material in my database. The database is basically thousands of single purchasing transactions:

 

 

I have created a measure (ASP) using two other measures (total sales and total quantity). I can put this measure with account names in a table and it will summarize and show me the Average Sales Price for that material.

I can also do the same thing and add Account Name and my table will now tell me the Average Sales Price for a material by a specific Account Name:

 

What I want to do is be able to divide the ASP shown in the first table (by material) with the one in the second table (by Account Name) in order to see whether the Account pays higher or lower than the average. 

 

Does anyone have any idea on how to do this? Thank you so much in advance !

 

Best,

Jorge

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi jorge_mejia,

    Please create a measure using the similar formulas.

    Average=CALCULATE(AVERAGE(Table[Sales Price]),ALLEXCEPT(Table,Table[material]))


    I test using my sample table.



    Create a measure using the formula using the formula below and get expected result.

    Average = CALCULATE(AVERAGE(Table1[value]),ALLEXCEPT(Table1,Table1[Material]))



    Best Regards,
    Amgelia


1 Reply

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi jorge_mejia,

    Please create a measure using the similar formulas.

    Average=CALCULATE(AVERAGE(Table[Sales Price]),ALLEXCEPT(Table,Table[material]))


    I test using my sample table.



    Create a measure using the formula using the formula below and get expected result.

    Average = CALCULATE(AVERAGE(Table1[value]),ALLEXCEPT(Table1,Table1[Material]))



    Best Regards,
    Amgelia