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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Saaharjit
Helper I
Helper I

HOw to divide values using the total of a column

This is my dataset. I want to add a new column that divides all the values from sales column by the total(2,35,887.04).

 

For eg the first row of the the new column would by 1,550,688/2,35,887.04 * 100 = 65.8%.

 

 

 

1 ACCEPTED SOLUTION

Hi @Saaharjit,

Create the following measures in your table.

SumValuebygroup = CALCULATE(SUM(Table3[Value]),ALLEXCEPT(Table3,Table3[Group],Table3[Date]))
Sumvaluebydate = CALCULATE(SUM(Table3[Value]),ALLSELECTED(Table3[Date]))
Percent = [Sumvaluebydate]/[SumValuebygroup]

1.PNG2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8

Check my Slouction 

Create 2 Measures
1st : Total Sales = CALCULATE(Sum('Sheet1 (3)'[Sales]),All())
2nd : Sales % = (Sum('Sheet1 (3)'[Sales])/[Total Sales])*100divide values using the total of a column.png

 

v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @Saaharjit

I make a test using the sample data below.
1.PNG


Create a measure in the table using DAX below.

SumValuebygroup = CALCULATE(SUM(Table3[Value]),ALLEXCEPT(Table3,Table3[Group]))

Then create a column suing the following formula and create a Matrix visual to display the result.

%percent = Table3[Value]/[SumValuebygroup]
2.PNG


If the above steps don’t help, please share sample data and post expected result here.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply @v-yuezhe-msft. This helps but my problem is that i have a date slicer. So when i select a date period it does not calculate as per the time period specified. It returns the total for the original data. 

 

 

Hi @Saaharjit,

Create the following measures in your table.

SumValuebygroup = CALCULATE(SUM(Table3[Value]),ALLEXCEPT(Table3,Table3[Group],Table3[Date]))
Sumvaluebydate = CALCULATE(SUM(Table3[Value]),ALLSELECTED(Table3[Date]))
Percent = [Sumvaluebydate]/[SumValuebygroup]

1.PNG2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msftThanks alot for your help

This is my calculated column 

 

Column Total = var temp = Incomestate[sub_group_name]
return CALCULATE(sum(Incomestate[actual_2]),ALL(Incomestate),Incomestate[sub_group_name]=temp)

 

I am getting the wrong values here though. Not able to figure out why

 

Capture.JPG

 

The first value in the new column should be 186,392 but its showing something else.

 

I am doing this so i can divide the total of each "sub_group_name" by each of its valiue to calculate % of sales

vanessafvg
Super User
Super User

if think this is the same as this one?

 

http://community.powerbi.com/t5/Desktop/Create-measure-of-product-sales/m-p/173357#M75701

 

you dont need to * 100, in modelling just set the measure to %





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I have multiple layers. This is just one. I don't want to divide by the total of all the layers.

 

For eg in the above situation, "Net Sales" is one layes in the table. Now i want to divide the tolal of just "Net Sales" by each column in Net Sales.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.