topic Creating Conditional Measurements in DAX Commands and Tips

Creating Conditional Measurements

Anonymous — Wed, 06 May 2020 18:10:23 GMT

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:

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

camargos88 — Wed, 06 May 2020 18:50:07 GMT

Anonymous ,

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

Anonymous — Wed, 06 May 2020 19:22:47 GMT

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]))