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]

Hello,

Trust you are doing good. Many thanks for your response and supports .

I will provide feedback tomorrow after opening the file.

I just posted one Calculation function request for help. I will appreciate your intelligent opinion on the appropriate PowerPivot measures to use.

Best Regards

[Ashish_Mathur]

Hi,

Share the link of that post.

[st-dat]

Hello,

Trust your was good.

All my efforts failed to open the pbix file.

My second request below require a measure to derive a single value.....

I have two tables: AAA and BBB
Request: - To count number of department in table BBB that exist in table AAA department
where table AAA Faculty name  starts with 'two lower case letters'
Expected Result is Nine (9) Departments
1st Table name is ----  AAA
	FACULTY	DEPARTMENT
	pgSCIENCE	Biology
	SCIENCE	Biology
	pgSCIENCE	Chemistry
	SCIENCE	Chemistry
	pgSCIENCE	Physics
	ARTS	Law
	pgARTS	Geography
	ARTS	Literature
	pgARTS	Law
	pgARTS	Literature
	pgARTS	Social
2nd Table name  is -- BBB
	SCHOOL	DEPARTMENT
	pgSCIENCE	Biology
	SCIENCE	Biology
	pgSCIENCE	Chemistry
	SCIENCE	Chemictry
	pgSCIENCE	Physics
	ARTS	Law
	pgARTS	Geography
	ARTS	Literature
	pgARTS	Law
	pgSCIENCE	Botany

[Ashish_Mathur]

Hi,

The PBI file is opening just fine from the link that i shared with you.  I do not understand your expected result.  How can the answer be 9 when the departments in Table BBB are only 8? 

[st-dat]

You are super correct, thanks

Please share the statements of the measure used here. I spent most part of my day searching for online guide on how to open pbix file on excel. It wasnt your fault or that the solution is wrong but my lack of understanding how to manage the opening of the file on excel 2016.

Regards 

[Ashish_Mathur]

Hi,

Download and install PowerBI desktop (for free) from the Microsoft website and then just double click to open the PBI file from the link i shared with you.

[st-dat]

Many thanks for your kindness.. I will update you as soon as it is done.

Please intervene my other request resent today....I suspect it requires a count and filter functions I could not manage properly.

Best Regards

[Ashish_Mathur]

Please read my previous post carefully.  I have asked a question there.

[st-dat]

Your question:  How can the answer be 9 when the departments in Table BBB are only 8? 

It is not 9, the correct result out output should be 5...(the departments in table BBB that exist in table AAA and having Faculty name starting with two lower case latter).

pgSCIENCE	Biology
pgSCIENCE	Chemistry
pgSCIENCE	Physics
pgARTS	Geography
pgARTS	Law

I'm sorry for the misleading final value and inadequate explanation

Thank you

[Ashish_Mathur]

Hi,

You may download my solution workbook from here.

[st-dat]

Hi,

Thank you so much and have a good day.

[Ashish_Mathur · Accepted solution]

You are welcome.  Have a good day.

[st-dat]

Hi,

I have 2 tables: AAA and BBB
Request: - To count number of department name 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]

Great work! Thanks

Another issue, I don't know if it's PowerPivot bug or my error, when you double-click to drill down a figure e.g. 20 as displayed on the report sheet generated from the PowerPivot excel table, it will show a 1000 rows instead of 20 rows based on report figure.  

What could be the cause and possible solution...

Unfortunately, i have no sample to show here......I'm looking forward to a good class to improve my knowledge in Measures and Power Bi

Thank you

[Ashish_Mathur]

You are welcone.  if my reply helped, please mark it as Answer.  The drill down feature of a PowerPivot Table is quite poor.  The way you are experiencing it is hwo it works.  The only solutio is to create another Pivot Table from the Data Model and apply simple Pivot Table filters of Top n.

[st-dat]

Many thanks, I will reply with feedback on monday.

Kind regards

[st-dat]

It may be possible to copy and paste this sample table as i could not get it linked. Thank you

Item Number	Date	Supplier Name	Number of Order	Amount	Product
1	01/09/2018	Supplier-1	20	2000	GROCERY
2	01/10/2018	Supplier-2	2	200	HARDWARE
3	01/11/2018	Supplier-3	4	400	HARDWARE
4	01/12/2018	Supplier-4	5	500	HARDWARE
5	01/13/2018	Supplier-5	50	5000	HOMECARE
6	01/14/2018	Supplier-6	25	2500	HOMECARE
7	01/15/2018	Supplier-7	30	3000	GROCERY
8	01/16/2018	Supplier-8	34	3400	GROCERY
9	01/17/2018	Supplier-9	40	4000	GROCERY
10	01/18/2018	Supplier-10	15	1500	GROCERY
11	01/19/2018	Supplier-11	40	7000	GROCERY
12	01/20/2018	Supplier-12	20	2000	nameTOBEDETERMINE
13	01/21/2018	Supplier-13	30	3000	abccNOTKOWN
14	01/22/2018	Supplier-14	3	300	xwyzNAME
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

[st-dat]

Could you copy the file in this format. I will keep trying if I can send a link.

Thank you

Item Number	Date	Supplier Name	Number of Order	Amount	Product
1	01/09/2018	Supplier-1	20	2000	GROCERY
2	01/10/2018	Supplier-2	2	200	HARDWARE
3	01/11/2018	Supplier-3	4	400	HARDWARE
4	01/12/2018	Supplier-4	5	500	HARDWARE
5	01/13/2018	Supplier-5	50	5000	HOMECARE
6	01/14/2018	Supplier-6	25	2500	HOMECARE
7	01/15/2018	Supplier-7	30	3000	GROCERY
8	01/16/2018	Supplier-8	34	3400	GROCERY
9	01/17/2018	Supplier-9	40	4000	GROCERY
10	01/18/2018	Supplier-10	15	1500	GROCERY
11	01/19/2018	Supplier-11	40	7000	GROCERY
12	01/20/2018	Supplier-12	20	2000	nameTOBEDETERMINE
13	01/21/2018	Supplier-13	30	3000	abccNOTKOWN
14	01/22/2018	Supplier-14	3	300	xwyzNAME
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

[st-dat]

I'm not sure windows 7 running on my computer can share file

[st-dat]

Hello

I'm trying to figure out the sharing of the file requested from the home network

Thanks for your kindness