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.

[st-dat]

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

[st-dat]

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'

[Ashish_Mathur]

Hi,

Which of the two is your requirement:

1. Computing the single figure of Top 5/Top 10 spend; or
2. Identifying which Suppliers are the Top 5/Top 10; or
3. Both of the above

Please also share the link from where i can download your PBI file.

[st-dat]

3- both

[Ashish_Mathur]

Share the link from where i can download your PBI file.

[st-dat]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI solution file from here.

Hope this helps.

[st-dat]

Hello Ashish,

I'm most delighted for the wonderful support. The solutions works perfectly.

[Ashish_Mathur]

You are welcome.

[st-dat]

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

[Ashish_Mathur]

Share the link of that post.

[st-dat]

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

[Ashish_Mathur]

That is not the link of the post.  Click and check.

[st-dat]

Oh so sorry for the mixup, see below

https://community.powerbi.com/t5/Desktop/Help-to-Calculate-Previous-and-Current-Fiscal-Periods/m-p/5...

Thanks

[st-dat]

Thnak you so much, I will confirm the outcome on monday when I will be able open the pbix file sent.

Warm Regards

[Ashish_Mathur]

You are welcome.

[st-dat]

I work only on PowerPivot 2016, hope the solution will open and compatible for use on the platform....

[Ashish_Mathur]

Yes.  Just redo all of it in Excel 2016.  It will work.

[st-dat]

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.

[Ashish_Mathur]

Hi,

The measure has already been created in the PBI file shared with you yesterday.