Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I am trying to build up a tracking systems to manage our client. So far I have calculated a cumulative column that reset each FY. Now I can see the cumulative number of jobs for each month. For every 150 jobs, the clients received a gift.
I would like to do few things: How do I group then into bin of 150 with the cumultive resetting for the next month when I have reached the 150 mark?
Is there a way in BI to mark the event where there reach their target?
I would like for the column to display TICKET when the 150s is reached in another column next to the month in consideration
The left over after 150 needs to be distributed to the following month until we reached again the 150 and it needs to reset again This needs to happen by clients I hope someone can really healp here.
Thanks team.
Solved! Go to Solution.
Please find the link :
https://www.dropbox.com/s/vjeaoyqqnqhwtao/Carry.pbix?dl=0
I am getting 56 in Aug. So not able to get how you got 126.
@corange any chance you can provide a sample data in a format that can be copy pasted in excel and clearly show your desired outcome in a separate column?
Hi,
I believe I need a new measure that will add my carry over from the month prior to the current month.
E.g. AUGUST = 368 + 53
I will then have to modified my quotient formula with the new measure to get the right result.
Hi,
Please find attached the document with a couple of examples and the desired outcome.
@corange can't get in the sharepoint as I am not in this sharepoint directory. Can you share this by G drive
Hi,
No worries. Here you are : https://docs.google.com/spreadsheets/d/1j96YXXCbobiUCz_sXiXedIZKxlSE_tcODF9_xYxJzKI/edit?usp=sharing
I really hope you can help me here. I know it can be done but I have got no clue where to have the carry over points changed based on that 150 bracket.
Please find the link :
https://www.dropbox.com/s/vjeaoyqqnqhwtao/Carry.pbix?dl=0
I am getting 56 in Aug. So not able to get how you got 126.
Hi, Thank you.
It did work. For me to obtain the specific number of prizes to be given in a month, I calculated the number of tickets that were received the month prior and substracted it.
Yes it is 56.
I am goign to try your solution now. The only thing I dont want it to do is to do a cummulative of the prizes. At the moment your solution keeps adding the prize from the previous month to the current and so on. I would like to just have what they need to be given that month. Can I use an aggregation and substract what was given last month to the current?
@corange see if it works for you
Hi,
Thank you. I have tried your solution and looked at the results you obtained for the prizes per month but it is not matching what I am expecting. Like in the first date for example, we should only have 2 prize then the carry over but your example shows 3.
Try to use MOD
https://docs.microsoft.com/en-us/dax/mod-function-dax
mod([Cumm Total],150)
https://docs.microsoft.com/en-us/dax/quotient-function-dax
quotient([Cumm Total],150)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
Hi,
Thanks for your reply.
It doesnt really work for me. Because each month there is a value and I dont necessary reach the 150 bookings.
This is how the table presents:
On the line highlighted in yellow, this is where we should be giving a prize, then the 7 bookings is carried to the next month and added to the new bookings for that month. If it is was a round 150 then it will reset at 0. I will also like to be able to track when we have given the prize or when we should with maybe conditional formatting?
This is what I have got in my head but not even sure we can actually do this on BI.
When you take Mod means remainder, then it will show you 7 at the last in feb.
The quotient should tell no of price
Hi,
Thank you again. I did understand your explanation and i believe I was unclear on my answer.
I would like the carry over to be integrated so it gives me the right number of prize to give for the month.
In the example I have highlighted:
203 /150 = 1 prize
(368 + 53) / 150 = 2 Tickets - carry over is 121 bookings
(289 + 121) / 150 = 2 prizes
I should be giving 2 prizes in Sept not 1 as the carry over means that we have reached 150 bookings twice due to the carry over.
Not sure if I have explained this a bit better.
Thanks for your help. I really appreciate it.
The should be one cumulative total which does not reset
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Not use this for both points and price.
Points = MOD([Cumm Sales],150)
Rewards = Quotient([Cumm Sales],150)
In case you want to move around date try, this one I have not tested. Say take point one month ahead or behind.
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,dateadd(date[date],1,month) <=maxx(date,date[date])))
To Share: You can create dummy data in excel and try to replicate your output on that and share the excel.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |