need help

[rohitjmd]

i am working on a respondent data where i have names of apps installed along with thier category. Could anyone suggest how to bulid a graph to show cateogry wise counts for these cuts- more than 10 apps installed, 8-10 apps installed, 7 apps, 6 apps and so on. eg: if shopping is the category, i want to see count of users with more than 10 apps, 8-10 apps and so on.

App Name	Category
AppStation	LIFESTYLE
HiCare	TOOLS
PayPal	FINANCE
Child Support	PRODUCTIVITY
Bitkong
Dropbox	PRODUCTIVITY
com.android.incallui
com.android.settings
Adobe Acrobat	PRODUCTIVITY
OneDrive	PRODUCTIVITY
'Domino''s'	LIFESTYLE
Facebook	Social Networking
Secure Folder	BUSINESS
Excel	PRODUCTIVITY
Android Auto	AUTO_AND_VEHICLES
Google+	SOCIAL
RainbowKey	PERSONALIZATION
Sound Meter	TOOLS
Toluna	SOCIAL
Hearthstone	GAME_CARD
Messenger	COMMUNICATION
Samsung Internet	COMMUNICATION
com.android.noisefield
com.samsung.android.allshare.service.mediashare
com.samsung.android.app.gestureservice
com.sec.hearingadjust
Calculator
Harry Potter	GAME_ADVENTURE
'Salsa''s Fresh Mex'	LIFESTYLE
Snapchat	SOCIAL
Youi	FINANCE
Sydney & NSW News	NEWS_AND_MAGAZINES
One Line Advanced	GAME_PUZZLE
SBTV	ENTERTAINMENT
OneNote	PRODUCTIVITY
Amazon Shopping	SHOPPING
PowerPoint	PRODUCTIVITY
English Listening Speaking	EDUCATION
CommBank	FINANCE
Spotify Music	Music
Email
Clock
OneDrive	PRODUCTIVITY
Spotify Music	Music
Edge	FINANCE
Protractor	TOOLS
S Voice
com.android.bluetoothmidiservice
com.android.defcontainer
com.android.wallpaper.livepicker
com.osp.app.signin
com.samsung.android.intelligenceservice2
com.samsung.android.sm.devicesecurity
com.sec.android.providers.iwlansettings
com.sec.android.widgetapp.dualclockdigital
com.sec.enterprise.knox.cloudmdm.smdms
com.sec.setdefaultlauncher
Settings
Downloads
Netflix	Entertainment
7plus	ENTERTAINMENT
ViewFruit	SOCIAL
Google Play services	TOOLS
Write on PDF	PRODUCTIVITY
i-Say	ENTERTAINMENT
FlyHelper
com.sec.android.app.bcocr
com.sec.android.app.snsimagecache
com.sec.android.app.wfdbroker
Camera
Messenger	COMMUNICATION
Pixel Color	ENTERTAINMENT
YouTube - Watch Upload and Share Videos	Photo & Video
Game Launcher
Instagram	Photo & Video
Raiz	FINANCE
Wish - Shopping Made Fun	Shopping
Snapchat	Photo & Video
Wish - Shopping Made Fun	Shopping
Instagram	Photo & Video
TuneWiki
Snapchat	Photo & Video
Wish - Shopping Made Fun	Shopping
Tinder	LIFESTYLE
Malwarebytes	TOOLS
AusPost	BUSINESS
YouTube - Watch Upload and Share Videos	Photo & Video
Facebook	Social Networking
QR Scanner	TOOLS
WasteOfSpace	GAME_SIMULATION
Messages
Outlook	PRODUCTIVITY
Genies & Gems	GAME_PUZZLE
Belong	TOOLS
FlyHelper | License Key
Collage Maker Pic Grid	PHOTOGRAPHY

[Ashish_Mathur · Accepted solution]

Hi,

That's easy but then for any category you select, there will only be one bar because there will only be one number per category.  Please clarify.  More importantly, please review the numbers in the left hand side table and met me know whether they are correct.

[Ashish_Mathur]

Hi,

Is this the result you are expecting?

[rohitjmd]

yes, but a graphical representation, kind of a histogram where i can show no of people fall in each bucket.

[Ashish_Mathur]

Do you want something like this.

[rohitjmd]

yes but categries as drop down not in legend . please suggest how you you did it.

[Ashish_Mathur · Accepted solution]

Hi,

That's easy but then for any category you select, there will only be one bar because there will only be one number per category.  Please clarify.  More importantly, please review the numbers in the left hand side table and met me know whether they are correct.

[rohitjmd]

can you suggest how i should do it in my file ?

[Ashish_Mathur]

Hi,

As requested in my previous message, please let me know whether my figures in the left hand side table are correct or not.

[rohitjmd]

no its not correct. i am sending the sample data again with a new field device id. So basically what i want to see here how many repsondent have more than 10 apps for a particular category, how many have 8-10 and so on. i hope my question is clear now. Please share your dax expression too as i am also trying to solve the problem and your code would help me.

data

[Ashish_Mathur]

There is no file at that link.

[rohitjmd]

hi Ashish here is the data,

Respondent IDDevice IDApp NameCategory

3be34727-042e-40f3-ace7-7c3a1135acfa	60af5f231c2f96d1	AppStation	LIFESTYLE
857d31c3-be20-4ab0-b70e-34389b0623ae	8c760f47e4669f29	HiCare	TOOLS
59a878c8-7086-4539-897c-f25a98581e20	16acb9ef91a78ef2	PayPal	FINANCE
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	Child Support	PRODUCTIVITY
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	Dropbox	PRODUCTIVITY
23d10ced-0e9a-455a-831b-862734fa4eb3	2024464d620de7a6	Adobe Acrobat	PRODUCTIVITY
76bfa0e9-a068-4408-9922-61dae0d18618	72fb80f822af845d	OneDrive	PRODUCTIVITY
857d31c3-be20-4ab0-b70e-34389b0623ae	8c760f47e4669f29	'Domino''s'	LIFESTYLE
0f46c1e6-def2-4595-abaf-fe738ce7ccc5	1CF34B7C-D057-4047-B110-4C6059B953B8	Facebook	Social Networking
1a350f86-8d6a-4ad4-b541-8653016674e7	eb8cf0459240c516	Secure Folder	BUSINESS
c3866aca-2232-465d-82f3-ea8726c9638a	ced9a13f65a1a9d	Excel	PRODUCTIVITY
c63353e2-baf5-4aa1-9937-b6000061cba9	3172de358bc937d5	Android Auto	AUTO_AND_VEHICLES
c63353e2-baf5-4aa1-9937-b6000061cba9	3172de358bc937d5	Google+	SOCIAL
c63353e2-baf5-4aa1-9937-b6000061cba9	3172de358bc937d5	RainbowKey	PERSONALIZATION
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	Sound Meter	TOOLS
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	Toluna	SOCIAL
66cc64e3-e745-47cf-a04e-7d4392000094	27d1b6f916a3f393	Hearthstone	GAME_CARD
66cc64e3-e745-47cf-a04e-7d4392000094	27d1b6f916a3f393	Messenger	COMMUNICATION
66cc64e3-e745-47cf-a04e-7d4392000094	27d1b6f916a3f393	Samsung Internet	COMMUNICATION
f3d3d13b-0cf3-4b61-a352-49a876dd8a65	a5770de1adad0b09	Harry Potter	GAME_ADVENTURE
371fba9f-111b-4edc-8572-3c1788f90bb6	1bb15eb461e6acf2	'Salsa''s Fresh Mex'	LIFESTYLE
a9273dd0-9055-49db-8fe9-1551e9216bf5	160b182b4d2ce2f0	Snapchat	SOCIAL
e1445c28-db2e-452b-99ee-7cc3d0724067	778d557c85d20aaa	Youi	FINANCE
c3866aca-2232-465d-82f3-ea8726c9638a	ced9a13f65a1a9d	Sydney & NSW News	NEWS_AND_MAGAZINES
0787145d-4c3c-4a78-9805-2d0fcc93706c	847581fab8476e2d	One Line Advanced	GAME_PUZZLE
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	SBTV	ENTERTAINMENT
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	OneNote	PRODUCTIVITY
5281627e-149d-4c30-8262-9d662301a553	58fd40c60463b70f	Amazon Shopping	SHOPPING
3be34727-042e-40f3-ace7-7c3a1135acfa	60af5f231c2f96d1	PowerPoint	PRODUCTIVITY
b5a6e7ac-a2db-4884-b838-9a6e050a5644	a7d3fc2c27cd0b5d	English Listening Speaking	EDUCATION
f3d3d13b-0cf3-4b61-a352-49a876dd8a65	a5770de1adad0b09	CommBank	FINANCE
691fcb32-5295-40a7-aa94-346f4af6e281	07947C0A-F351-4E5E-A034-40875EF58CE2	Spotify Music	Music
c3866aca-2232-465d-82f3-ea8726c9638a	ced9a13f65a1a9d	OneDrive	PRODUCTIVITY
7974b0bd-a4a3-426b-95ee-37687ac7f1df	A04B9787-49B0-4091-B82D-9D6AF2069573	Spotify Music	Music
9ce9faa7-ffbd-41a4-a565-ea3ba709a6eb	89c53367627e7739	Edge	FINANCE
8a29504f-1e32-47aa-b0e7-63095e9a9416	6ea936607ed7b48e	Protractor	TOOLS
0f46c1e6-def2-4595-abaf-fe738ce7ccc5	1CF34B7C-D057-4047-B110-4C6059B953B8	Netflix	Entertainment
d316fabd-f54f-40ca-b602-064dc36a6114	e96dc64262e2d60f	7plus	ENTERTAINMENT
c63353e2-baf5-4aa1-9937-b6000061cba9	3172de358bc937d5	ViewFruit	SOCIAL
a750543c-3024-430c-81aa-e6b924e29648	4c476b424c2390e2	Google Play services	TOOLS
0787145d-4c3c-4a78-9805-2d0fcc93706c	847581fab8476e2d	Write on PDF	PRODUCTIVITY
e1445c28-db2e-452b-99ee-7cc3d0724067	778d557c85d20aaa	i-Say	ENTERTAINMENT
1546c587-cdb0-430c-8fc8-6addb735349c	2265b512b72f5b9a	Messenger	COMMUNICATION
29e303fe-f2e9-4460-b35b-f1c703e3fbff	b92114bfa68d0203	Pixel Color	ENTERTAINMENT
b8767189-ea2f-4a34-98ff-45f9dcb607a1	C6D8168E-8E78-4C5A-8644-DFD2DFD075F5	YouTube - Watch Upload and Share Videos	Photo & Video
3782f793-8cde-42b6-aab4-f52f14e1ea57	15D24C90-DF4C-4651-8EDE-25FBD58D9890	Instagram	Photo & Video
908723cd-e6a2-4082-9af0-e30baa865970	9961bf6e7afcd5fa	Raiz	FINANCE
aff1579e-aca2-4807-8f14-e1ec7d4ac1e7	52D93782-8A4C-47D1-B0D3-353319409108	Wish - Shopping Made Fun	Shopping
3782f793-8cde-42b6-aab4-f52f14e1ea57	15D24C90-DF4C-4651-8EDE-25FBD58D9890	Snapchat	Photo & Video
dee981bc-9d32-4299-90f6-432544557bc8	D7371A1D-083C-4B74-9E8A-6DE7ADB21672	Wish - Shopping Made Fun	Shopping
f702703f-b293-4212-a215-0c7dacff0eb4	BDD481F0-1585-46EC-8959-543572807692	Instagram	Photo & Video
f7dc9555-ada9-4fbe-84b4-475010f3d280	3A5950B9-9ECE-4FCF-9BBD-E5ECB91E0023	Snapchat	Photo & Video
473c853e-fca3-4827-8cb2-df576a5b7884	DDE2C857-5AA5-46F7-9A70-AAE501067CD7	Wish - Shopping Made Fun	Shopping
9bcae8c4-a775-4dbd-830a-708fbb77fd6a	f3861eafdac7a3aa	Tinder	LIFESTYLE

[Ashish_Mathur]

Hi,

Is this the result you are expecting?  Please check the numbers.  If my numbers are wrong, then share the correct numbers for some Categories so that i can rewrite my formula and tally my answer with your expected result.

[rohitjmd]

hi Ashish, 

thanks for your reply.

though rather than counting the unique repspondents, i want to see how many same respid appears for a cateogory which have different apps installed. a respid represents a user id so if i am having a same user id for more than 10 times for a category for different apps, i need to count that user once in no of user with more than 10 apps bucket.

below pic is to show you the outcome required in case of app category social.

[Ashish_Mathur]

Hi,

Is this the result you are expecting?  Could you share a much larger dataset and the actual bucket sizes.  This is for me to check the efficiency of the formula.  Also, on the larger dataset, please share the sample output for 1 or 2 categories.  Lastly, if there are more column that you want to include in the dataset (say Country) and filter the table by a specific country, then share that result of 1-2 categories for that specific country as well.

[rohitjmd]

Hi Ashish ,

Thx for your reply.yes it first look your solution seems right. i need this data only on category filter  for now. as per your comments i am sharting the large data set and expected result for 2 categories with actual bucket required,

https://1drv.ms/x/s!AqSgJWIdQo7Tiwht9pn6g5A54HC6?e=AxGtOh

[Ashish_Mathur]

Hi,

My solution works.  Please allow me time until Sunday to share my solution with you.

[rohitjmd]

Thanks Ashish. I would wait for your reply.

[Ashish_Mathur]

Hi,

You may refer to my solution at this Blog article.

Hope this helps.

[rohitjmd]

thank you Ashsih for your help here. however i am not able to see the solution as access is denied.

[Ashish_Mathur]

You are welcome.  Your Office's firewall policies are coming in the way.  Try to download the file from your Home Computer.

[rohitjmd]

i tried it with my phone, and with my personal account as well, but failed.:(