Slice with IF, Slicer with Values to select, Resulting in a Calculation

[dkalina97]

I have a somewhat convoluted problem that I could easily solve in Excel but I need the processing power of BI. To simplify, the company makes a general assumption that a piece of equipment can produce 10 widgets daily. So if a plant produces 100 widgets, it is assumed the location has 10 machines. We have numerous locations, but i have a measure that calculates machines per location.

However, a location may be effecient and produce 100 units, but they only have 8 machines. I would like the user to be given the option of using the calculated machines, or "inputting" the actual. I have created a seperate table listing values 1 - 20 as the actual machines.

My thought would be a slicer to determine, use calculated, or use actual.

If use actual, then use the drop down on a second slicer to allow the user to select the actual units.

The result in the visual would be widgets per unit. The assumption would be 10 widgets per machine, but the user selects, use actual, and then selects 8 machines, and thus it would be 12.5 units per machine.

Or is there a better way?

HELP!  

[dkalina97]

In Excel i would simply have an input call and if(Input = "",assumed qty, input qty).

[v-zhangti]

Hi, @dkalina97 

Is it possible to share your example data in Excel? and what you expect the output to be. Please remove sensitive information in advance if you have it.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dkalina97]

Aboved is a very stripped down version of the customer summary. Essentially we sell 3 types of machines. It is assumed if the customer purchases the units for a type, they have 1. So for this account, there are 13 total locations, of which 7 are buying type 1 products and 10 are buying type 2. We know how many user sessions each sale provides, thus we know the assumed sessions per day. I would like the user to be able to override the actual number of machines (if it is known) so they can see an actual sessions per machine.

The attached for customer 13178 has 2 different unit types. I can calculate sessions per day and sessions per unit, with the assumption that they have 1 of each machine. But perhaps they actually have 3 machines, then the sessions per machine would change.

So I would like an option for a user to change the measure, so that rather than dividing buy the assumed quantity, they can select the actual units and use that as the denominator.

[dkalina97]

Customer Analysis.xlsx

[v-zhangti · Accepted solution]

Hi, @dkalina97 

I don't know what's wrong with my calculations and didn't get the 7.19 result. But you can refer to this method of selecting input values.

Measure = 
Var _Sessions=CALCULATE(SUM('Table'[Total Sessions]))
Var _Location=13
Var _Days=CALCULATE(DISTINCTCOUNT('Table'[Actual Ship Date]),FILTER(ALL('Table'),[Total Sessions]<>BLANK()))
return
_Sessions/_Location/_Days

Measure 2 = DIVIDE([Measure],SELECTEDVALUE('actual machines'[actual machines]))

Do you want the output of this?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.