Aggregation and dynamic filter

[js2246]

I am new to Power BI, having worked primarily with Tableau in the past. I am bringing in a couple data sets that I need to be able to merge on a summary level but I want to be able to maintain the filtering on the individual level. Does anyone know how I can do this?

I have two simplified example data sets that show individual Clients in 2016 and 2017, the products they purchased on a given date and the number of visits before purchase.

2016
Product A	Product B	Date	2016 Clients	2016 Visits
A	b	1/1/2016	1	2
A	c	4/16/2016	1	1
B	b	3/10/2016	1	3
B	b	2/12/2016	1	4
B	a	8/9/2016	1	1
A	c	6/6/2016	1	2
B	c	1/14/2016	1	5

2017
Product A	Product B	Date	2017 Clients	2017 Visits
A	c	4/3/2017	1	3
A	b	3/8/2017	1	4
A	b	9/16/2017	1	5
B	b	1/5/2017	1	4
B	a	8/7/2017	1	7
A	c	4/11/2017	1	8
B	c	10/30/2017	1	2

Granted we would have many more observations then this. I need the data to be originally on the individual level because I want the eventual user of the dashboard to be able to change what date ranges they want to look at. So I want a filter on the final output that can be interfaced with. However, I want the final results shown to be grouped by Product A, and Product B, with Clients and Visits to be summations. Then I have to be able to join the aggregated 2016 and 2017 tables on Product A and Product B so that I can do calculations across the years such as growth.

Product A	Product B	2016 Clients	2017 Clients	16-17 Client Growth	2016 Visits	2017 Visits	16-17 Visit Growth
A	b	1	2	100%	2	9	350%
A	c	2	2	0%	3	11	267%
B	a	1	1	0%	1	7	600%
B	b	2	1	-50%	7	4	-43%
B	c	1	1	0%	5	2	-60%

Is this possible in Power Bi?

[nickchobotar · Accepted solution]

Hello@js2246

So, @CahabaData  pretty much laid out the whole strategy for you. I went ahead and put the pbix together for you and since you are new to Power BI jumping into Power Query along with DAX might be a little overwhelming, so I created the solution only in DAX. (As Lincoln said one war at a time) . I just simply used UNION() function to append your 2016 and 2017 tables and then used some simple DAX CALCULATE()s to apply correct filters to your metrics.

Here is the download link:

https://1drv.ms/u/s!AsgNvkRwqGC7gwZj-6GQoXmWYFRW

Table = UNION( '2016', '2017' )

Clients 2016 = 
CALCULATE(
	SUM('Table'[ Clients]),
	YEAR('Table'[Date]) = 2016
)

Clients 2017 = 
CALCULATE(
	SUM('Table'[ Clients]),
	YEAR('Table'[Date]) = 2017
)

16-17 Client Growth = DIVIDE( [Clients 2017], [Clients 2016] ) -1

Visits 2016 = 
CALCULATE(
	SUM('Table'[ Visits]),
	YEAR('Table'[Date]) = 2016
)


Visits 2017 = 
CALCULATE(
	SUM('Table'[ Visits]),
	YEAR('Table'[Date]) = 2017
)

16-17 Visit Growth = DIVIDE([Visits 2017], [Visits 2016], 0) -1

[CahabaData]

Yes PBI can do this.  I would say most of us developers would not create a static display that is only good for the 2016-2017 period - but instead build a report/visual that is ongoing with rolling data so that it would be 'Current Year' and 'Last Year'.

My approach would be to append the year tables into 1, and in ongoing years to continue to do that.  Add a date table.

Your client count or visit count by product row (current year, last year) will be dax measures.