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.
1 – Top 5 grocery total spend or Total Sum of the Top 5 grocery item | ||||||
(First identify the 5 Top Grocery items and sum their amount = $26500 | ||||||
Expected Answer | 26500 | |||||
Supplier Name | Amount | |||||
grocery - 11 | 7000 | |||||
grocery-16 | 6000 | |||||
grocery - 20 | 5000 | |||||
grocery - 15 | 4500 | |||||
grocery-9 | 4000 | |||||
Expected Answer | $ 26,500.00 | |||||
2- Top 10 items total spend | ||||||
Expected Answer | $ 58,800.00 | |||||
Supplier Name | Number of Order | Amount | ||||
Supplier-18 | 95 | 9500 | ||||
Supplier-19 | 82 | 8200 | ||||
Supplier-17 | 72 | 7200 | ||||
Supplier-16 | 60 | 6000 | ||||
Supplier-11 | 40 | 7000 | ||||
Supplier-20 | 45 | 5000 | ||||
Supplier-15 | 45 | 4500 | ||||
Supplier-13 | 30 | 3000 | ||||
Supplier-8 | 34 | 3400 | ||||
Supplier-5 | 50 | 5000 | ||||
Expected Answer | $ 58,800.00 | |||||
3 - Nnumber of Orders placed to TOP 10 Suppliers and total dollars spend | ||||||
Answers expected | 559 | $ 59,400.00 | ||||
Item Number | Date | Supplier Name | Number of Order | Amount | Product | |
15 | 01/23/2018 | Supplier-15 | 45 | 4500 | Grocery | |
16 | 01/24/2018 | Supplier-16 | 60 | 6000 | Grocery | |
17 | 01/25/2018 | Supplier-17 | 72 | 7200 | hardMEDICARE | |
18 | 01/26/2018 | Supplier-18 | 95 | 9500 | childMEDICATION | |
19 | 01/27/2018 | Supplier-19 | 82 | 8200 | adultCAREDRUG | |
20 | 01/28/2018 | Supplier-20 | 45 | 5000 | GROCERY | |
9 | 01/17/2018 | Supplier-9 | 40 | 4000 | GROCERY | |
11 | 01/19/2018 | Supplier-11 | 40 | 7000 | GROCERY | |
13 | 01/21/2018 | Supplier-13 | 30 | 3000 | abccNOTKOWN | |
5 | 01/13/2018 | Supplier-5 | 50 | 5000 | HOMECARE | |
Answers expected | 559 | $ 59,400.00 | ||||
4 – Top 5 items total spend | ||||||
Answer expected | $ 37,900.00 | |||||
Item Number | Date | Supplier Name | Number of Order | Amount | Product | |
17 | 01/25/2018 | Supplier-17 | 72 | 7200 | hardMEDICARE | |
18 | 01/26/2018 | Supplier-18 | 95 | 9500 | childMEDICATION | |
19 | 01/27/2018 | Supplier-19 | 82 | 8200 | adultCAREDRUG | |
11 | 01/19/2018 | Supplier-11 | 40 | 7000 | GROCERY | |
16 | 01/24/2018 | Supplier-16 | 60 | 6000 | Grocery | |
Answer expected | $ 37,900.00 | |||||
5- Top 3 computer products total spend (Computer products are products that start with four lower Case letters) | ||||||
Item Number | ||||||
Answer expected:- | $ 24,900.00 | |||||
Date | Supplier Name | Number of Order | Amount | Product | ||
17 | 01/25/2018 | Supplier-17 | 72 | 7200 | hardMEDICARE | |
18 | 01/26/2018 | Supplier-18 | 95 | 9500 | childMEDICATION | |
19 | 01/27/2018 | Supplier-19 | 82 | 8200 | adultCAREDRUG | |
Answer expected:- | $ 24,900.00 | |||||
6 - Drill down through double-clicking the report data of 30 is | ||||||
displaying 1000 rows on excel instead of actual 30. | ||||||
Kindly help with solution |
Solved! Go to Solution.
Hi,
You may download my PBI solution file from here.
Hope this helps.
You are welcome. Have a good day.
I have 2 tables: AAA and BBB | ||||||||
Request: - To count number of department names in table BBB that exist in table AAA department | ||||||||
where table AAA Faculty name contains 'PG' | ||||||||
Expected Result is Nine (9) Departments | ||||||||
Table name ---- AAA | ||||||||
FACULTY | DEPARTMENT | |||||||
Science-PG | Biology | |||||||
Science | Biology | |||||||
Science-PG | Chemistry | |||||||
Science | Chemistry | |||||||
Science-PG | Physics | |||||||
Arts | Law | |||||||
Arts-PG | Geography | |||||||
Arts | Literature | |||||||
Arts-PG | Law | |||||||
Arts - PG | Literature | |||||||
Arts - PG | Social | |||||||
Table name -- BBB | ||||||||
SCHOOL | DEPARTMENT | |||||||
Science-PG | Biology | |||||||
Science | Biology | |||||||
Science-PG | Chemistry | |||||||
Science | Chemictry | |||||||
Science-PG | Physics | |||||||
Arts | Law | |||||||
Arts-PG | Geography | |||||||
Arts | Literature | |||||||
Art-PG | Law | |||||||
Science - PG | Botany | |||||||
Hello Ashish,
The second solution for the question below also works as expected.
Many thanks to Ashish and the forum team
I have 2 tables: AAA and BBB | ||||||
Request: - To count number of department names in table BBB that exist in table AAA department | ||||||
where table AAA Faculty name contains 'PG' |
Hi,
Which of the two is your requirement:
Please also share the link from where i can download your PBI file.
3- both
Share the link from where i can download your PBI file.
Hello,
Thanks for your help.
Having revewed your last question, my request is 'Computing the single figure of Top 5/Top 10 spend'
Please ignore my earlier response where I indicated 'both'
I hope the master fact table will be useful as my effort failed to send the link requested. I'm working from home using windows 7 and office 2007.
Note: My post contains expected result as a single figure. For clarity, I included the table extract from the master table to arrive at the expected result.
Esteem Regards
Hi,
You may download my PBI solution file from here.
Hope this helps.
Hello Ashish,
I'm most delighted for the wonderful support. The solutions works perfectly.
You are welcome.
Hi Ashish,
One more thing today, I have post a request for help and will appreciate your usual intervention...... I have a Total Sum of Sales and need calculate result for the following period.
I need to Find the TOTAL SALES during the period below:
1) Last Week
2) Current Week To Date
3) Last Fiscal Month
4) Current Fiscal Month To Date
5) Last Fiscal Quarter
6) Current Fiscal Quarter todate
7) Last Fiscal Year
😎 Current Fiscal Year To date
Thank you so much
Share the link of that post.
Hi Ashish,
Below is the link to my post, it is same as the question in my recent message to you.
https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
Thanks
I will appreciate your valuable contribution, inspite input from someone who suggested some website to read for solution
That is not the link of the post. Click and check.
Oh so sorry for the mixup, see below
Thanks
Thnak you so much, I will confirm the outcome on monday when I will be able open the pbix file sent.
Warm Regards
You are welcome.
I work only on PowerPivot 2016, hope the solution will open and compatible for use on the platform....
Yes. Just redo all of it in Excel 2016. It will work.
Hello,
Just came to mind, could you help create a measures for the solution so that it can re-use often to address other needs such applying it to different periods and with other tables.
Loking forward to your response. I'm most delighted for your help.
Hi,
The measure has already been created in the PBI file shared with you yesterday.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |