cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee

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]

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.
8 REPLIES 8
Helper I

Check my Slouction

Create 2 Measures
1st : Total Sales = CALCULATE(Sum('Sheet1 (3)'[Sales]),All())
2nd : Sales % = (Sum('Sheet1 (3)'[Sales])/[Total Sales])*100

Microsoft Employee

Hi @Saaharjit

I make a test using the sample data below.

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]

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.
Helper I

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.

Microsoft Employee

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]

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.
Helper I

Helper I

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

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

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!

Helper I

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.