Search code examples
daxdaxstudio

Dynamic filter and calculations from different tables?


I am new to powerbi and looking forward to solve this table that I am trying to do. I do have 3 tables and the example table is listed below.

Users table is connected as one to many with product and tickets table

Users 1-----* tickets

Users 1-----* products

My issue is on the Sales Amount it will create for sure a duplicate amount of each product id. I am trying to create a measure that will get the distinct count of the productid and divide it to the sum of the Sales Amount if the productID is duplicated and those

The desired output is with the last table that I posted. which is dividing the duplicate of sales amount by the counts of duplicate and the rest will be the same.

Thank you so much in advance for the help!

Table 1 Users Table

Sales Rep      Owner ID
Mark           markid01
Joy            joy01

Table 2 product table

Ticket No.  product ID  Sales
XXX005474   productID1  $100 
XXX005475   productID2  $200 
XXX005476   productID3  $300 
XXX005477   productID4  $400 
XXX005478   productID5  $500 
XXX005479   productID6  $600 
XXX005480   productID7  $700 

Table 3 Tickets

productTicket   ownerID
XXX005490   markid01
XXX005491   markid01
XXX005492   markid01
XXX005493   joy01
XXX005494   joy01
XXX005495   joy01
XXX005496   joy02

Selected columns in powerbi

Ticket No.  product ID      Owner ID   Sales Rep    Sales Amount      Output that I need 

XXX005474   productID34534  markid01    Mark        $32,400           $32,400 
XXX005475   productID00000  markid01    Mark        $100              $20
XXX005476   productID00000  markid01    Mark        $100              $20
XXX005477   productID00000  markid01    Mark        $100              $20
XXX005478   productID00000  markid01    Mark        $100              $20
XXX005479   productID00000  markid01    Mark        $100              $20
XXX005480   productID00001  markid01    Mark        $10,400           $10,400 
XXX005481   productID00002  markid01    Mark        $10,200           $10,200 
XXX005482   productID00003  markid01    Mark        $5,580            $5,580 
XXX005483   productID00004  markid01    Mark        $5,008            $5,008 
XXX005484   productID00005  markid01    Mark        $4,470            $4,470 
XXX005485   productID00006  markid01    Mark        $3,900            $3,900 
XXX005486   productID00007  markid01    Mark        $3,250            $3,250 
XXX005487   productID00008  markid01    Mark        $0                $0 
XXX005488   productID00009  markid01    Mark        $0                $0 
XXX005489   productID00010  markid01    Mark        $0                $0 
XXX005490   productID00011  markid01    Mark        $0                $0 

Solution

  • Repeated_X = 
    
                    Var vDuplicates = CALCULATE(
                    COUNT('Demo Data'[product ID]), 
                         FILTER('Demo Data', 'Demo Data'[product ID] = EARLIER('Demo Data'[product ID])))
                    Var vSales = 'Demo Data'[Sales]
                    RETURN
                    DIVIDE(vSales,vDuplicates)