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
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)