Measures and Date Slicers

[kF1894]

We have developed a productivity scorecard that assigns a point value to each measure based on where the measure falls in the range. The measures are On Time & Close Rate; shown by employee with year and month slicers. Easy enough – We then take the point value and weight each at 50% to determine the composite score</SPAN></SPAN>

Example:</SPAN></SPAN>
On Time Close Rate </SPAN></SPAN>
Measure 93.90% 96.40%</SPAN></SPAN>
Points 75 150 </SPAN></SPAN>
Weight 0.5 0.5 </SPAN></SPAN>
Composite 112.5</SPAN></SPAN>

=if(ISBLANK(orglistSC[CR PTS]),orglistSC[CR PTS],orglistSC[CR PTS]*0.50)+if(ISBLANK(orglistSC[OT PTS]),orglistSC[OT PTS] ,orglistSC[OT PTS]*0.50)</SPAN></SPAN>
The issue is now they have asked us to weight the close rate at 100% for 1^st quarter, and go back to 50/50 for the rest of the year.</SPAN></SPAN>

If I use =Calculate(if(ISBLANK(orglistSC[CR PTS]),orglistSC[CR PTS],orglistSC[CR PTS]*0.50)+if(ISBLANK(orglistSC[OT PTS]),orglistSC[OT PTS] ,orglistSC[OT PTS]*0.50),</SPAN> Dash_DateTbl[Quater]=1) </SPAN></SPAN>

Each month works via the slicer, but selecting multiple (Jan-June) does not work.</SPAN></SPAN>Any help would be appreciated.</SPAN></SPAN>

 

[Tianbas]

How about adding a weight table with one row per month instead of the fix 0.5 value in your formula ?

 

[kF1894]

It's worth a shot, I'll see what happens. thanks

 

[jafa1970]

The Weight table is probably a better idea, as it will provide greater flexibility going forward, but this might work for the immediate fix:


Suggest you need a calculate for each option: (formatted by Dax Formatter by SQLBI)

=
CALCULATE (
    IF (
        ISBLANK ( orglistSC[CR PTS] ),
        orglistSC[CR PTS],
        orglistSC[CR PTS] * 0.50
    )
        + IF (
            ISBLANK ( orglistSC[OT PTS] ),
            orglistSC[OT PTS],
            orglistSC[OT PTS] * 0.50
        ),
    Dash_DateTbl[Quater] <> 1
)
    + CALCULATE (
        IF (
            ISBLANK ( orglistSC[CR PTS] ),
            orglistSC[CR PTS],
            orglistSC[CR PTS]
        ),
        Dash_DateTbl[Quater] = 1
    )

You may also need a filter on the specific year in there if you have data for Q1 in earlier years.

One question - why do you need the ISBLANK function? If the value is blank, it will not return anything and will not affect the result. Would be interested to see if the following produces the same result for your original formula:
=(orglistSC[CR PTS]*0.50)+(orglistSC[OT PTS]*0.50)

If this does work, you can probably remove them from the CALCULATE formula above too. The spelling on your date table for Quarter is incorrect too.

Cheers
Phil