The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |