Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
I'm trying to replicate a calculation I have in excel into Power BI.
Long story short, I have a table which display what the performance is for each month (see screen shots) and a second column YTD where it displays the performance depending on the month.
i.e. If we had 100% in July and 120 % in August, then the YTD column would display 100% for July and 110% for August as August is the sum of July and August percentages divided by 2.
Then if it was september, it would be divided by 3 and so on.
I know how to create this calculation in Excel, but would any of you know how to do in Power BI?
See screen shots
Solved! Go to Solution.
@RodrigoTXRAHere is something I put together to give you an idea, logically you should have a FIscal Date DImension in model for time intelligence and this kind of calculation, but I guess this will get you started.
Cheers!!!
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@RodrigoTXRACan you share the sample data set and will get back to you with the solution. Thanks!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Parry
Thanks for the quick reply, please find below the dataset.
When i paste, the headers are deleted but it is as follows:
Sales Rep | User ID | FY Appraisal Month | Appraisal Month % | YTD | Sort | Quarter
Full Name | xxxxxxxxxxxxx | Jul-18 | 100.00% | 100.00% | 1 | Q1 |
Full Name | xxxxxxxxxxxxx | Aug-18 | 120.00% | 110.00% | 2 | Q1 |
Full Name | xxxxxxxxxxxxx | Sep-18 | 126.21% | 115.40% | 3 | Q1 |
Full Name | xxxxxxxxxxxxx | Oct-18 | 120.00% | 116.55% | 4 | Q2 |
Full Name | xxxxxxxxxxxxx | Nov-18 | 110.00% | 115.24% | 5 | Q2 |
Full Name | xxxxxxxxxxxxx | Dec-18 | 120.00% | 116.04% | 6 | Q2 |
Full Name | xxxxxxxxxxxxx | Jan-19 | 110.00% | 115.17% | 7 | Q3 |
Full Name | xxxxxxxxxxxxx | Feb-19 | 130.00% | 117.03% | 8 | Q3 |
Full Name | xxxxxxxxxxxxx | Mar-19 | 110.00% | 116.25% | 9 | Q3 |
Full Name | xxxxxxxxxxxxx | Apr-19 | 140.00% | 118.62% | 10 | Q4 |
Full Name | xxxxxxxxxxxxx | May-19 | 137.00% | 120.29% | 11 | Q4 |
Full Name | xxxxxxxxxxxxx | Jun-19 | 130.00% | 121.10% | 12 | Q4 |
@RodrigoTXRAwill look at this later today and get back to you.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Is your YTD definition is Jan - Dec or different?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k, our Financial Year goes from July to June the following year.
i.e. From 01st July 2017 to 30th June 2018
@RodrigoTXRAHere is something I put together to give you an idea, logically you should have a FIscal Date DImension in model for time intelligence and this kind of calculation, but I guess this will get you started.
Cheers!!!
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Apparently I cannot send too many private messages, please see below
@parry2k - "Great, thanks Parry - I don't think there is an area where I can upload a file so I pasted it below.
I have inserted three users in this list below but I will have lists with 10 users at time.
In regards to the learning curve, I hope one day to be able to also give it back to the community, I'm building my skill sets at the moment and attending many meet ups with Power Bi users.
Sales Rep | User ID | FY Appraisal Month | Appraisal Month % | Sort | Quarter |
Mary Jane | ijkl123 | Jul-18 | 107.40% | 1 | Q1 |
Mary Jane | ijkl123 | Aug-18 | 120.00% | 2 | Q1 |
Mary Jane | ijkl123 | Sep-18 | 126.21% | 3 | Q1 |
Mary Jane | ijkl123 | Oct-18 | 120.00% | 4 | Q2 |
Mary Jane | ijkl123 | Nov-18 | 110.00% | 5 | Q2 |
Mary Jane | ijkl123 | Dec-18 | 120.00% | 6 | Q2 |
Mary Jane | ijkl123 | Jan-19 | 110.00% | 7 | Q3 |
Mary Jane | ijkl123 | Feb-19 | 130.00% | 8 | Q3 |
Mary Jane | ijkl123 | Mar-19 | 110.00% | 9 | Q3 |
Mary Jane | ijkl123 | Apr-19 | 140.00% | 10 | Q4 |
Mary Jane | ijkl123 | May-19 | 137.00% | 11 | Q4 |
Mary Jane | ijkl123 | Jun-19 | 130.00% | 12 | Q4 |
John Smith | efgh123 | Jul-18 | 107.40% | 1 | Q1 |
John Smith | efgh123 | Aug-18 | 120.00% | 2 | Q1 |
John Smith | efgh123 | Sep-18 | 126.21% | 3 | Q1 |
John Smith | efgh123 | Oct-18 | 120.00% | 4 | Q2 |
John Smith | efgh123 | Nov-18 | 110.00% | 5 | Q2 |
John Smith | efgh123 | Dec-18 | 120.00% | 6 | Q2 |
John Smith | efgh123 | Jan-19 | 110.00% | 7 | Q3 |
John Smith | efgh123 | Feb-19 | 130.00% | 8 | Q3 |
John Smith | efgh123 | Mar-19 | 110.00% | 9 | Q3 |
John Smith | efgh123 | Apr-19 | 140.00% | 10 | Q4 |
John Smith | efgh123 | May-19 | 137.00% | 11 | Q4 |
John Smith | efgh123 | Jun-19 | 130.00% | 12 | Q4 |
Rodrigo Teixeira | abcd123 | Jul-18 | 107.40% | 1 | Q1 |
Rodrigo Teixeira | abcd123 | Aug-18 | 120.00% | 2 | Q1 |
Rodrigo Teixeira | abcd123 | Sep-18 | 126.21% | 3 | Q1 |
Rodrigo Teixeira | abcd123 | Oct-18 | 120.00% | 4 | Q2 |
Rodrigo Teixeira | abcd123 | Nov-18 | 110.00% | 5 | Q2 |
Rodrigo Teixeira | abcd123 | Dec-18 | 120.00% | 6 | Q2 |
Rodrigo Teixeira | abcd123 | Jan-19 | 110.00% | 7 | Q3 |
Rodrigo Teixeira | abcd123 | Feb-19 | 130.00% | 8 | Q3 |
Rodrigo Teixeira | abcd123 | Mar-19 | 110.00% | 9 | Q3 |
Rodrigo Teixeira | abcd123 | Apr-19 | 140.00% | 10 | Q4 |
Rodrigo Teixeira | abcd123 | May-19 | 137.00% | 11 | Q4 |
Rodrigo Teixeira | abcd123 | Jun-19 | 130.00% | 12 | Q4 |
@parry2k After applying the changes to the other templates I realised that whenever I have two people on the list (The dataset I sent you only had one person), the measures sum up for both users.
i.e. see screen shot below:
It picks up the correct % column, however, on the YTD Avg HR column, it is adding up the percentage for user 1 and user 2
Would you have any thoughts? Or are able to replicate the issue on your end?
@RodrigoTXRAwe need to update DAX formul to seperate by user, I will get back to you soon.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k unbelievable, fantastic solution. It indeed worked for me. I required to make some changes as my Month column was displaying the date type as text for the month description, so i just went back to the original settings and it worked well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |