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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors