Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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%.
Solved! Go to 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]
Thanks,
Lydia Zhang
Check my Slouction
Create 2 Measures
1st : Total Sales = CALCULATE(Sum('Sheet1 (3)'[Sales]),All())
2nd : Sales % = (Sum('Sheet1 (3)'[Sales])/[Total Sales])*100
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
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]
Thanks,
Lydia Zhang
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
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 %
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
83 | |
72 | |
49 |
User | Count |
---|---|
143 | |
130 | |
108 | |
64 | |
55 |