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...
  • nickchobotar's avatar
    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