Forum Discussion

js2246's avatar
js2246
Frequent Visitor
9 years ago
Solved

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 fi...
Need Help
  • nickchobotar's avatar
    nickchobotar
    9 years ago

    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-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's avatar
CahabaData
Icon for Memorable Member rankMemorable Member
9 years ago

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.