Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcantor
Community Champion
Community Champion

Weekly membership level calculation

We do a friendly competition between customer care teams. the calculations up until this point have been calculated on a monthly basis but were calculated incorreclty due to membership attribution. We allow training to reduce the divisor for points scoring. So, if a rep has been here less than 60 days, they are at a member ship level of 0. After that, they move up to a membership level of 0.5. On the 90th day, they have a membership level of 1.

The numerator is a calculation based upon performance value including calls, orders, and invoices.  We call that calculation [Attribution Amount]. My problem is getting the calculation to correctly attibute membership and correctly roll up the numbers. The actual calculation is Performance Average = DIVIDE([Attribution Amount], [Membership]).

when using this calculation, it applies the same membership divisor accross the totals instead of as it changes within the month. I would like to create a weekly calculation that can be summed up. I believe I may need to create a table showing the weekly membership number but I have lost my way.

So far, I have added a column to the fact table showing the number of days since training began. Then I added another column showing membership status for the day the transaction occured. this give me the correct line level membership status. How can I use that level of granularity to calcuate points on a weekly basis and then roll it up into a monthly total?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




4 REPLIES 4
CheenuSing
Community Champion
Community Champion

Hi @kcantor

 

Can you please share some data and the output expected on OneDrive and provide a link

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@kcantor 

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.

The question is no longer relevant. At the time, we just held points until the new month began. That was a lifetime ago. I could probably solve it now that I have more experience but I am no longer working with that data nor could I provide a file. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors