Creating Conditional Measurements

[rderrickwhite]

I have a simple measure that is as follows:

AVG QTY PRODUCED = COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 5 DAY])

What I need to accomplish is to make this measure conditional, based on the site:

If the Site equals 21201, then the formula should be:  

AVG QTY PRODUCED = COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 4 DAY])

Otherwise, it should be:

AVG QTY PRODUCED = COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 5 DAY])

There is a need to look at the information as a whole, and separately, so I don't think separate measures will work.

Link to project mockup:

https://1drv.ms/u/s!Ar8mMAEgH9mzaiaJKQo9b7ZOR1I 

This has been keeping me up at night.  Any help is appreciated.

[camargos88 · Accepted solution]

@rderrickwhite ,

Have you tried hard code on it?

AVG QTY PRODUCED =

VAR _site = SELECTEDVALUE(TABLE[SITE])

RETURN

IF _site = 21201;

COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 4 DAY]);

AVG QTY PRODUCED = COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 5 DAY]))

Did I answer your question? Mark my post as a solution!
Ricardo

[camargos88 · Accepted solution]

@rderrickwhite ,

Have you tried hard code on it?

AVG QTY PRODUCED =

VAR _site = SELECTEDVALUE(TABLE[SITE])

RETURN

IF _site = 21201;

COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 4 DAY]);

AVG QTY PRODUCED = COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 5 DAY]))

Did I answer your question? Mark my post as a solution!
Ricardo

[rderrickwhite · Accepted solution]

@camargos88 

That's a good idea.  This is what worked.

AVG QTY PRODUCED =

VAR _site = SELECTEDVALUE('SHIPMENTS OUT'[SITE])

RETURN

IF(_site = 21201,

COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 4 DAY]),

COUNT('SHIPMENTS OUT'[ORDER_NO])/SUM('CALENDAR'[DAYS WORKED 5 DAY]))