Hello all!
I am looking to understand how I can create a measure that will produce a score when certain criteria have been met. Most of the scenarios will be around dates.
To keep things simple, as an example - if a customer has completed “Activity A” in the last 3 months, they get 5 points. Then if they have completed “Activity B” in the last 6 months, they get 10 points. I then need to be able to total up the points to get an overall score. The measure will need to return a zero if there is no date present for the activity. It will also have to return a zero if the time period has passed.
I couldn’t find any examples but I am sure I had seen something out there.
This is a major project for me in 2019 so I’d appreciate any insight or help.
I plan to create a brand new dataset for the reports, so blank canvas all round.
Thanks in advance
Pete
Interesting scenario on this one.
The way to solve this will likely come down to setting up a supporting table that contains all this logic within it.
Then once you have this you will need to iterate through every customer and check if these meet any of the criteria for the points system.
Here’s some examples that basically set out the exact strategy to use for you.
The table you will need will likely need to have a bit of detail, for example Activity Name/ Days from today / Points Amounts.
Then using the techniques shown above you need to every customer through every single row in your new supporting table (or secondary table) and check off if it calculated to TRUE.
See how you go with these ideas. Maybe set something up and show where you get to. Then we can work from there if there any further help required.
The great thing is, this is very doable and can get it solved pretty quickly for you.
Chrs
Hi Sam,
Thanks for the help. I have managed to get this working to a certain extent with my first metric. But I am seeing an issue with Blanks returning the selected value.
The first metric was to check if the Last Upsell occurred within the last 180 days. If it was the customer gets a score of 20.
Measures to calculate Days since Last Upsell:
Date of Last Upsell = LASTDATE(Opportunities[Est Close Date])
Days Since Last Upsell = IF (ISBLANK([Date of Last Upsell]), BLANK(),
VALUE(TODAY()- [Date of Last Upsell]))
From here I created a secondary table for my Health Metric
To return the Score I used the following measure:
Upsell Score =
CALCULATE(SELECTEDVALUE('Health Score Metrics - Days'[Health Score], BLANK()),
FILTER(ALL('Health Score Metrics - Days'),
[Days Since Last Upsell] >='Health Score Metrics - Days'[Min Number of Days] &&
[Days Since Last Upsell] < 'Health Score Metrics - Days'[Max Number of Days])
)
This works but all the Blanks also get 20?
I will have similar metrics to the above but also others that are very different. The goal being to sum all the scores together to get a total.
Do you think this is the best way to approach this?
Thanks
Pete
What if you just wrap the formula with some simple logic like the below.
IF( ISBLANK( Date of Last Upsell ), BLANK(), measure.......
This should solve this issue.
Also make sure the change the format of the last sale measure to date. It seems pointless to have the time there as well.
In you table do you also have a customer context? It’s hard for me to tell because it’s not added to the image at all?
Understanding all the context of the calculation here is crucial when working on things like this.
Hi Sam,
That worked for the blanks, thanks!
Yes the first column in the table is the Customer. I created the second metric in exactly the same way but had to create a second metric table for the Selected Value. Is there a way to use the one table rather than creating a single table for each metric? Either way, it worked and allowed me to add the two scores together to get a total score.
I’m happy to proceed this way if I have to but if you feel there is a better way I’d love to know.
Regards
Pete
Not 100% sure what you mean by second metric table for the selected value?
If that is another supporting table then that should be fine. If you can logically place them into one table I also don’t see an issue with that either.
Chrs
Hi Sam,
So my Health Score table has been a great success, thanks for the help! But I am now coming stuck on a few things I want to do.
The first is to create an average score. The second is to create a trend metric.
I have a feeling that the way I ended creating the total score is restricting me from doing further calculations.
My total score is calculated by adding all the individual health metrics together.
Health Score = [Upsell Score]+[BWR Score]+[Services Score]+[Cases Score]+[Webinar Reg Score]+[Champion Score]+[Executive Sponsor Score]+[Stakeholder Score]
The problem now is I can’t figure out how to get a total of all the Health Scores to divide by the number of customers? When I add the totals on the table is just see the highest score.
I have not yet looked into how to create a trend yet as this is the first priority.
Any suggestions? Happy to share the PBIX if that helps.
Regards
Pete
Probably will need to see an example file on this one.
I’ve read over the post a few times now, but it’s getting up there in terms of complexity.
Will need to complete some testing to get a good solution I think.
Thanks
Hi all,
Sam asked me to share the PBIX in order to help me out with my questions.
PBIX File
Thanks
Pete
