Forum Discussion
Aggregation and dynamic filter
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?
Hellojs2246
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-6GQoXmWYFRWTable = 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
2 Replies
- nickchobotar
Skilled Sharer
Hellojs2246
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-6GQoXmWYFRWTable = 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
Memorable Member
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.
