Forum Discussion

argnist's avatar
argnist
Frequent Visitor
8 years ago
Solved

Divide summarized column by another summarized column

Hi all,

 

does anyone know the way to divide one summarized column over to another summarized column?

 

When I try something like SUM (Total Sales) / SUM (Items Sold) it calculates it for each item (for Laptop 1, Laptop 2, etc.) instead of the for the group of items (Laptop and Phones)

 

 

Here is a Matrix visual I have in Power BI (the 3rd column, which I need to calculate is simply Total Sales/Items Sold)

 

Item CategoryTotal SalesItems SoldAverage per item
Laptops         970,000              9701000
Phones         500,000              625800

 

 

This is how the original data set looks like:

 

ItemTotal SoldItems Sold
Laptop 170012
Laptop 280010
Laptop 3100014
Laptop 495010
………
Phone 170024
Phone 275065
Phone 380017
………

 

My question might be confusing - but please let me know if I can explain more.

  • vcastello's avatar
    vcastello
    8 years ago

    Hi argnist

    Try the following:

    1.- Click New Measure.

    2.- Enter
             Average Per Item = DIVIDE([Total Sales], [Items Sold])

    3.- Put that new measure in the matrix

    Hope That Helps

    Vicente

4 Replies

  • vcastello's avatar
    vcastello
    Resolver III
    8 years ago

    Hi argnist

    Try the following:

    1.- Click New Measure.

    2.- Enter
             Average Per Item = DIVIDE([Total Sales], [Items Sold])

    3.- Put that new measure in the matrix

    Hope That Helps

    Vicente

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    argnist,

     

    Please try the DAX below.
    Average per item = SUM(Table1[Total Sold])/SUM(Table1[Items Sold])

     

    Regards,

    Charlie Liao