linking slicers' preassigned values to DAX

[saqib1]

@andhiii079845 

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 🙂

[lbendlin · Accepted solution]

see attached

[lbendlin]

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

[saqib1]

@lbendlin  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 · Accepted solution]

see attached

[saqib1]

@lbendlin Thanks for your time. Actually, there's a bit of customization needed. The kilograms column is related to 'Days' column:

Days	kilograms
10	1
11	2
12	3
13	4
14	5
15	6
16	7
17	8
18	9
19	10

There are two distinct periods which need to be selected to know the amount of Raw material required to produce the output. What’s needed is that three more slicers give the user option to choose the starting days and the two time periods. And the corresponding values from Kilograms column get added up and multiplied to the factors weightages mentioned in previous question.

Expected Outcome:

One slicer (Start) of starting day e.g 11th day to start. Two slicers (T1 & T2) for two distinct time periods. Say for e.g. one slicer has period selected of 3 days and the other has selection of 4 days. So if user selects Start slicer as 11, T1 = 3, T2 = 4, and the Business unit type = B, Location = F, Plant Efficiency  =I, then outcome should be as follows:

Kilograms corresponding to Start (slicer), 11th day = 2

T1+T2= 3+4= 7, therefore kilograms of 7 more days will have to be added = 2+(3+4+5+6+7+8+9)= 44 Kilograms

Factor weightages:

B= 0.95

F= 0.85

I=  0.85

End Result = 44 * 0.95*0.85*0.85 = 30.2 kgs

The modified DAX for the above calculation is requested.

[saqib1]

really appreciate it if someone could guide on the revised query please...

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[saqib1]

@lbendlin can you kindly share your email? I'm unable to attach (or drag) the pbix file in the reply section. it says "the file type (.pbix) is not supported"

[lbendlin]

Sorry, can't do that.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[saqib1]

@lbendlin Kindly see the attached link

https://1drv.ms/u/s!AoqwAyWfotQsjGyIi3BStwNfDVN2?e=teJilI 

Raw material consumption needs to be determined such that when a user selects the slicer "T Start", the Kilograms column corresponding to the days number of that selection gets selected. Then Only after T Start selection, T1 can be selecetd and then T2 can be selected.  

One more issue that needs to be solved is that once I select an option from Business unit, location, or, plant efficiency, the other options (eg D,E,F,G,H,I etc disappear).

[lbendlin · Accepted solution]

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.