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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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