Count Items

[NFI27]

Hello,

This is question is something that i have done similar in excel the problem is i dont know if it could be done in bi. But better.

I want to create like an index catalogue to know how many items i manage per product line but it goes a bit deeper.

Example  Table:

Item	Product Line	Category	subcategory	Division
ad	sport	water sport	diving	snorkle
af	sport	water sport	surfing	wax
ag	sport	water sport	surfing	surfboard
ah	sport	land sport	football	ball
aj	sport	land sport	basketball	jersey
ak	school & office	school	paint	acrylic
al	school & office	office	adhesives	glue
zx	school & office	school	paint	oil
zc	school & office	school	writing instrument	pencil
zv	school & office	office	adhesives	scotch tape

This is the table. Now using a slicer i want to be able to count :

SLICER: 

Product Line

Sport

school&Office

(FOR EXAMPLE IF I CLICK ON SPORTS IT SHOULD SHOW ME)

Catergory	Count item
water sport	3
land sport	2

( IF I CLICK ON WATER SPORTS ITEMS IT SHOULD SHOW ME)

Subvategory	Count item
Diving	1
Surfing	2

( IF I CLICK ON THE SURFING ITEMS IT SHOULD SHOW ME)

Division	Count Item
Wax	1
Surfboard	1

and this would be the end.

Sorry, if this an impossible task just wondering if it could be done.

Thanks in advance

[Ashish_Mathur]

Hi,

I suggest that you create a Matrix visual and drag the last four fields to the row labels.  Write this measure

=COUNTA(Data[Item])

[NFI27]

Hello,

It works partly.

as in it counts the items but does not get past the prouct line, so the table ends up being like this:

Product Line	count
Sport	5
School & office	5

Cant filter through it

[Ashish_Mathur]

Hi,

There are filter controls at the bottom right hand side of the visual - see image below

[NFI27]

It worked. Thanks. Now a continuation to this question.

I have added sales and years. i want to separate each year to know how many items were sold each year.

Right now i have this:

		2016 Measure	2016 Sales	2017 Measure	2017 Sales	2018 Measure	2018 Sales	TOTAL Measure	TOTAL Sales
Sport		48 items	12600	48 items	37000	48 items	35000	48 items	84600
	water sport	38	5600	38	24000	38	20000	38	49600
	diving	13	2600	13	15000	13	1000	13	27600
	snorkle	13 (items	2600	13 (items	15000	13 (items	10000	13 (items	27600
	Surfing	25	3000	25	9000	25	10000	25	22000
	wax	10(items	1000	10(items	4000	10(items	8000	10(items	13000
	surfboard	15 (items	2000	15 (items	5000	15 (items	2000	15 (items	9000
	Land Sport	30	7000	30	13000	30	15000	30	35000
	football	20	4000	20	8000	20	7000	20	19000
	ball	20(items	4000	20(items	8000	20(items	7000	20(items	19000
	basketball	10	3000	10	5000	10	8000	10	16000
	jersey	10(items	3000	10(items	5000	10(items	8000	10(items	16000

But i want to get this:

		2016 Measure	2016 Sales	2017 Measure	2017 Sales	2018 Measure	2018 Sales	TOTAL Measure	TOTAL Sales
Sport		26 items	12600	59 items	37000	52 items	35000	68 items	84600
	water sport	18	5600	32	24000	30	20000	38	49600
	diving	7	2600	10	15000	12	1000	13	27600
	snorkle	7 (items	2600	10 (items	15000	12 (items	10000	13 (items	27600
	Surfing	11	3000	22	9000	18	10000	25	22000
	wax	6(items	1000	10(items	4000	9(items	8000	10(items	13000
	surfboard	5(items	2000	12 (items	5000	9(items	2000	15 (items	9000
	Land Sport	8	7000	27	13000	22	15000	30	35000
	football	5	4000	15	8000	18	7000	20	19000
	ball	5(items	4000	15(items	8000	18(items	7000	20(items	19000
	basketball	3	3000	10	5000	6	8000	10	16000
	jersey	3(items	3000	10(items	5000	6(items	8000	10(items	16000

This way i am segregating each year and i know what items i sold wich year and at the end, i get the total sale with the total amount of items. and after i am done with this i want to add how much percentage does each category, sub-category, division. represent to my total sale like this:

		2016 Measure	2016 Sales	2017 Measure	2017 Sales	2018 Measure	2018 Sales	TOTAL Measure	TOTAL Sales		Participation   percentage
Sport		26 items	12600	59 items	37000	52 items	35000	68 items	84600	100%
	water sport	18	5600	32	24000	30	20000	38	49600	59%
	diving	7	2600	10	15000	12	10000	13	27600		33%
	snorkle	7 (items	2600	10 (items	15000	12 (items	10000	13 (items	27600			33%
	Surfing	11	3000	22	9000	18	10000	25	22000		26%
	wax	6(items	1000	10(items	4000	9(items	8000	10(items	13000			15%
	surfboard	5(items	2000	12 (items	5000	9(items	2000	15 (items	9000			11%
	Land Sport	8	7000	27	13000	22	15000	30	35000	41%
	football	5	4000	15	8000	18	7000	20	19000		22%
	ball	5(items	4000	15(items	8000	18(items	7000	20(items	19000			22%
	basketball	3	3000	10	5000	6	8000	10	16000		19%
	jersey	3(items	3000	10(items	5000	6(items	8000	10(items	16000			19%

I have done this in excel before.  but i am having a difficult time doing it in bi because it is not cell oriented.

Thanks in advance for the help.

[Ashish_Mathur]

Hi,

I do not understand.  Please share your actual dataset and show the expected result.

[NFI27]

Hello Ashish,

The dataset is too large too share, i am sending an excel replica

The result i want is this: (Keep in mind the red area)