Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
argnist
Frequent Visitor

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.

1 ACCEPTED SOLUTION
vcastello
Resolver III
Resolver III

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

View solution in original post

4 REPLIES 4
v-caliao-msft
Employee
Employee

@argnist,

 

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

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

vcastello
Resolver III
Resolver III

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

Anonymous
Not applicable

Hi I have also faced the same issue but could not understand your solution but i think you have solved the problem which is similar like mine. Could you please hel me out to calculate to summarized columns. I have attached screenshot and pbix file for the same. THank you so much

 

https://app.powerbi.com/groups/me/reports/27a39b38-d2ce-4efa-acc5-90c7be2eb65f?ctid=6e747d17-46a5-45...

Calculate two columns.PNG

Hi Kulchandra,

 

Try ....

Measure1 = DIVIDE(DIVIDE([Count Of Badges ID],[Duration Seconds]),60)

 

Hope That helps

 

Vicente

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.