Forum Discussion
Helper IIlinking slicers' preassigned values to DAX
Can you kindly guide on this query? A certain product can be made through different amounts of input depending upon several factors. DAX is needed to ascertain the amount of input required to produce the product based on the influencing factors. What’s required is that the user selects options from the different factors through slicers , and the DAX calculates the input required according to preassigned values of each factor which are multiplied by the standard amount required to produce one unit.
Business unit type
A = 1 B = 0.95 C = 0.85
Location
D = 1 E = 0.95 F = 0.85
Plant Efficiency
G = 1 H = 0.95 I = 0.85
Example:
Standard is 100 kg required to produce one unit of output.
But based on business unit A, Location E, Plant efficiency I, the actual input should be 100 * 1*0.95* 0.85 = 80.75 kg.
Slicers for Business unit type, Location, and Plant efficiency are needed such that by selecting a specific option from each slicer, the resultant value gets multiplied by the standard input required per unit.
Appreciate your time and guidance 🙂
see attached
RM = var a =[T Start Value] var b = [T Start Value]+[T 1 Value]+[T 2 Value] var c = CALCULATE(sum('Days n Kgs'[kilograms]),'Days n Kgs'[Days]>=a,'Days n Kgs'[Days]<=b) return c*SELECTEDVALUE('Business Units'[RM])*SELECTEDVALUE('Location Weightage'[RM])*SELECTEDVALUE('Plant Efficiency'[RM])Your other requirements (slicer dependencies) cannot be achieved since you have no meaningful data model.
10 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
saqib1lbendlin Thanks alot for responding 🙂
Following are the tables regarding the data:
Business Unit Type RM reqd A 1 B 0.95 C 0.85 Plant EffIciency RM reqd G 1 H 0.95 I 0.85 Location RM reqd D 1 E 0.95 F 0.85 One unit of the product needs 100 kg Raw material to be produced (as a standard). But the three factors above, namely Business unit type, location, and Plant Efficiency can alter the amount of RM required to produce one unit of output.
Expected Outcome:
Slicers are needed for each of the above factors. When a user selects an option from the slicers (A-I), the corresponding amount of RM reqd column of that particular factor should get multiplied with the standard amount of RM reqd for producing one unit of output.
Example:
If Business Unit type = B
Plant Efficiency = I
Location = F
then, Raw material required should be = 100*0.95*0.85*0.85 = 68.64 kg
The DAX for this calculation is requested.
- lbendlin
see attached