Forum Discussion
need help creating a scoring system that will allow more than one selection on a slicer
DATE PLANT RTM Complete
| 3/7/2020 | OS | REGION JM | 1 |
| 3/11/2020 | CG | REGION SO | 1 |
| 3/4/2020 | LX | REGION JK | 1 |
| MK | REGION JM | 0 | |
| UP | REGION SO | 0 | |
| 1/31/2020 | WL | REGION JK | 1 |
| TH | REGION JK | 0 | |
| 1/27/2020 | LU | REGION JK | 1 |
| VS | REGION JK | 0 |
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-yingjl
Community 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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.