Link 2 slicers with rows of a table and make the output (a specific cell) as input for another DAX

[saqib1]

Hi,

I want to know the Raw material consumed in making a product whose RM input varies according to formulation and also varies per day according to the days taken to complete. I want to select a row from the left column (Formula No.) and middle column (Duration) from the following table through 2 slicers:  

Formula No.	Duration (Days)	Daily Avg. RM Cons. Growth %
30	8	2.75
30	9	2.67
30	10	2.60
31	1	3.09
31	2	3.04

And resultantly the respective row from the right column (Daily Avg. RM Growth %) should become the output. 

Assuming the previous formulation, Formula 29 (not in above table) takes 100 kg to produce one unit of product, if the user selects Formula No. 30 and  8 days Duration, then to calculate the RM Consumed, the resultant value from the right column of 2.75% alongwith the duration (in days) will  be used in a DAX function which is an annuity formula:

Total RM Consumed = 100*[[{(1+i)^(d)}-1]/i]

where i = Daily Avg. RM Growth %

          d = Duration (days)

substituting above values, the solution should be:

100*[[{(1.0275)^(8)}-1]/0.0275] = 881.4 kg

I want both i and d to be dynamically updated through slicers in the above (annuity) formula in DAX. Kindly guide how this may be done.

[andhiii079845 · Accepted solution]

Hi,

my solution. Perhaps you have correct it for the percent value. I take the values from the table and have to divide by 100. In your formula example you use the other format (0.0X instead of X (%)) 

Measure = 
VAR __dur = SELECTEDVALUE('table'[Duration])
VAR __RM = CALCULATE(VALUES('table'[RM]), FILTER('table','table'[Formula]=SELECTEDVALUE('table'[Formula])))
RETURN 100*(((1+__RM/100)^__dur)-1)/(__RM/100)

[andhiii079845 · Accepted solution]

Hi,

my solution. Perhaps you have correct it for the percent value. I take the values from the table and have to divide by 100. In your formula example you use the other format (0.0X instead of X (%)) 

Measure = 
VAR __dur = SELECTEDVALUE('table'[Duration])
VAR __RM = CALCULATE(VALUES('table'[RM]), FILTER('table','table'[Formula]=SELECTEDVALUE('table'[Formula])))
RETURN 100*(((1+__RM/100)^__dur)-1)/(__RM/100)

[saqib1]

Hi

I'm encountering an issue due to this static figure (in blue) after RETURN. It refers to the quantity of raw material required to produce one unit of output for a certain formula no.

RETURN 100*(((1+__RM/100)^__dur)-1)/(__RM/100)

But this also needs to be a dynamic figure since now whenever I select "1" as the Duration, the total raw material consumed always comes out to be 100. Even though it should vary according to formula no. as shown in the (truncated version) of the table, below:

Formula No.	Duration (Days)	Daily Avg. RM Cons. Growth %	RM reqd. per unit
30	8	2.75	110
30	9	2.67	110
30	10	2.60	110
31	1	3.09	115
31	2	3.04	115

How can the DAX be alteerd to incorporate for this dynamic figure?

[andhiii079845]

Bring the quantity of raw material column (rawquantity) in a own table (mass), put the column in a slicer. Edit the measure like this:

Measure = 
VAR __raw = SELECTEDVALUE('mass'[rawquantity])
VAR __dur = SELECTEDVALUE('table'[Duration])
VAR __RM = CALCULATE(VALUES('table'[RM]), FILTER('table','table'[Formula]=SELECTEDVALUE('table'[Formula])))
RETURN __raw*(((1+__RM/100)^__dur)-1)/(__RM/100)

[saqib1]

@andhiii079845 

Apologies, I got the wording incorrect.. The RM reqd per unit should not be dynamic (as in user being able to change it through slicer). rather it also has specific values for specific formula no. and duration.

so whats required is that user selects Formula No. and Duration through slicers, and the corresponding values from Daily Avg. Cons. growth% AND RM reqd. per unit become the output in the annuity formula DAX mentioned earlier.

[andhiii079845]

You have to add it as a column to your table and add in the measure. I am sure that you will be able to add it in the measure. if not show us where is your problem:)

[saqib1]

@andhiii079845 Still couldn't figure out how to resolve the issue..Following are screenshots of the pbix file. Two different tables RM Cons. detail and RM wrt Formula No. had to be merged since they had one to many relationship.. don't understand how to alter the DAX to solve the problem.    

[andhiii079845]

Are you able to share the PBI via onedrive or dropbox? 

[saqib1]

yes sure

[andhiii079845]

I wait for the PBI than 🙂

[andhiii079845 · Accepted solution]

You do not need the merge table, try this:

Total Raw Material Cons. =

Var _Formula = CALCULATE(maxx('RM Cons. Detail','RM Cons. Detail'[Formula No.]))

VAR __dur = SELECTEDVALUE('RM Cons. Detail'[Duration (Days)])

VAR __RMG = CALCULATE(VALUES('RM Cons. Detail'[Avg. RM Cons. Growth %]), FILTER('RM Cons. Detail','RM Cons. Detail'[Formula No.]=SELECTEDVALUE('RM Cons. Detail'[Formula No.])))

VAR _RMperUnit = CALCULATE(maxx('RM wrt Formula No.','RM wrt Formula No.'[RM per unit]),FILTER('RM wrt Formula No.','RM wrt Formula No.'[Formula No.]=_Formula))

--RETURN _RMperUnit

-- RETURN _Formula

 RETURN _RMperUnit*((((1+ __RMG/100)^(__dur))-1)/(__RMG/100))

"--..." are comments, you can look what are the values of the different parts of the calculation, swap the "--" to a another RETURN. 🙂

[saqib1]

100% Spot on! 🙂

Btw I'm unable to figure out how to see the breakup of calculation through the following:

"--..." are comments, you can look what are the values of the different parts of the calculation, swap the "--" to a another RETURN. 

Can you kindly elaborate a bit?

[andhiii079845]

Yes, if you want the see the different calculations steps change the 

Return variable e.g.

RETURN _RMperUnit

or RETURN _Formula

 or RETURN _RMperUnit*((((1+ __RMG/100)^(__dur))-1)/(__RMG/100))

[saqib1]

@andhiii079845 I'm unable to share the onedrive link of the pbix file here. can you kindly share your email plz?

[saqib1]

Excellent! Just tried it out. It works exactly as required. Thanks alot 🙂

[andhiii079845]

It seems that the Formula and duration determine the Daily Avg. RM Growth %, so there a fix combinations with both fields. For example there is no duration 11 with formula 30 only duation 8,9,10? Is this correct? 
So you have to create a table with the formula and duration and the Daily Avg. RM Growth %. 
With a measure you can calculate than the Total RM Consumed.
If you answer my first question, I can build this measure for you 🙂

[saqib1]

@andhiii079845  Thanks for replying 🙂 

Yes, it's correct.. Formula and duration both determine the Daily Avg. RM Growth %. and there is an entire table for the 3 columns but I truncated it for brevity. The Formula No.s are from 30 to 60 and the duration for each formula are from 1-10 days. The Daily Avg. RM Growth % is different for each row as shown in the above truncated table.

Hope I was able to explain what you were asking. 🙂