Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kasife
Helper V
Helper V

Total sales and target per product for each salesperson

Hi Guys,

I have two fact tables that are related by product. I need to fetch the total goal per product that is in the Goals table and relate it to the salesperson that is in the Productivity Table. How could I do?
In the image below, seller x made sales of the product arapongas and cianorte.

kasife_1-1687389733461.png

This is the result I would like to get without having to use any tracking. I would like to make it to measure

Seller x sold 64 units of arapondas and cyanorte products last month (goal 80), so the percentage sold is 80%

kasife_3-1687390092330.png

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@kasife , I think you need common date and product table in this case

 

example new table

Product= distinct(UNION(distinct(Sales[Product]), distinct(Productivity[Product])))

 

and then these type of measures can help

Total Sales = CALCULATE(SUM(Productivity[Sales]))

Total Target = SUM(Goals[Target])

Percentage Sold = DIVIDE([Total Sales], [Total Target])

 

 

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

 

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@kasife , I think you need common date and product table in this case

 

example new table

Product= distinct(UNION(distinct(Sales[Product]), distinct(Productivity[Product])))

 

and then these type of measures can help

Total Sales = CALCULATE(SUM(Productivity[Sales]))

Total Target = SUM(Goals[Target])

Percentage Sold = DIVIDE([Total Sales], [Total Target])

 

 

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

 

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

@amitchandak  I already have both tables, but it is bringing me the percentage of total sales and what I would like is to get the percentage only of the products that are related to each seller

kasife_0-1687437733642.png

When I select the products I can get the results

kasife_1-1687437900594.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.