Measures and Date Slicers

kF1894

New Member
Joined
Nov 20, 2009
Messages
7
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 1st 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

Board Regular
Joined
Apr 29, 2014
Messages
101
How about adding a weight table with one row per month instead of the fix 0.5 value in your formula ?
 

jafa1970

New Member
Joined
Feb 17, 2011
Messages
38
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)
Code:
=
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
 

Forum statistics

Threads
1,077,849
Messages
5,336,734
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top