PowerPivot Dax/Measures issues on TopN and Drill Down

[st-dat]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI solution file from here.

Hope this helps.