Forum Discussion

Mike-Hydrite's avatar
Mike-Hydrite
Frequent Visitor
6 years ago
Solved

need help creating a scoring system that will allow more than one selection on a slicer

DATE              PLANT     RTM             Complete

3/7/2020OSREGION JM1
3/11/2020CGREGION SO1
3/4/2020LXREGION JK1
 MKREGION JM0
 UPREGION SO0
1/31/2020WLREGION JK1
 THREGION JK0
1/27/2020LUREGION JK1
 VSREGION JK0

I have this very basic table recording the date that a safety meeting is conducted, the plant it was done at at the region that plant belongs to. I added a 1 for completed meetings and a 0 for uncompleted.

 

I am using a slicer show the 3 RTM options

REGION JK

REGION JM

REGION SO

 

If the region completes there meetings per quarter I need to show a score of 3, if they don't complete all there meetings I need to show a score of 1

 

Example

REGION JK has 5 plants. So to get a full score of 3 for Q1 then the meetings completed column shoud equal 5. Anything less is a 1

Both REGIONS JM and SO only have 2 plants each, so a full score of 3 can be obtained by having a meetings completed equal to 2 per Quarter.

 

I've had luck with getting the desired score of 3 or 1 when I use the slicer to go thru each region 1 at a time.

However, I also want the ability to select all 3 regions at the same time on the slicer and get a score of 3 or 1 depending on whether the all nine plants have had meeting in the quarter.  Meaning to get a 3 for Q1 (with all 3 regions selected) the total meeting count would have to equal 9.

This is where I cannot get the scoring right if more than one region is selected.

 

I have a calendar table in my model the has a quarter column that I'm using to filter to the correct quater.

  • Hi Mike-Hydrite ,

    If I got it correctly, you can create this measure to calcualte Sroce:

     

    Score =
    CALCULATE ( SUM ( 'Table'[Complete] ) * 3, 'Table'[Complete] = 1 )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Complete] = 0 )

     

    After you filter the quarter, when you select multi Regions in the slicer, you may get the result like this:

     

    Best Regards,
    Yingjie Li

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

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support

    Hi Mike-Hydrite ,

    If I got it correctly, you can create this measure to calcualte Sroce:

     

    Score =
    CALCULATE ( SUM ( 'Table'[Complete] ) * 3, 'Table'[Complete] = 1 )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Complete] = 0 )

     

    After you filter the quarter, when you select multi Regions in the slicer, you may get the result like this:

     

    Best Regards,
    Yingjie Li

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