Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I need to create a column from the value column with formula = standard material/ total sales
where type month and opco for std material and total sales are same as shown
please help as I am not being able to segregate this.
Here are the steps you can follow:
1. Create a calculated column.
Column =
var _value=CALCULATE(SUM('Table'[VALUE]),FILTER('Table',[Category]="STANDARD MATERIALS"&&[Type]=EARLIER('Table'[Type])&&[Months]=EARLIER('Table'[Months])))
var _total=CALCULATE(SUM('Table'[VALUE]),FILTER('Table',[Category]="TOTAL SALES"&&[Type]=EARLIER('Table'[Type])&&[Months]=EARLIER('Table'[Months])))
var _1=DIVIDE(_value,_total)
return IF([Category]="TOTAL SALES",_1,BLANK())
2. Result.
You can download the PBIX file from here.
Best regards
Liu Yang
If this post helps,then consider Accepting it as the solution to help other members find it faster.
@Anonymous
Is there a specific reason you need this is a column in your table? These sort of calculations are normally done using measures
Proud to be a Super User!
Paul on Linkedin.
sorry for late reply,
this formula has returned blank values.
@Anonymous , Create a new column in the table like this and try
new column =
var _SM = sumx(filter(table, [type] = earlier([type]) && [month] = earlier([month]) && [category] ="STANDARD MATERIAL"),[value])
return
if([category] ="TOTAL SALES" , divide(_SM,[value]))
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
177 | |
85 | |
70 | |
63 | |
55 |