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
carlenb
Helper II
Helper II

DAX - How can I divide each sale with total revenue? See model

Hi,

 

I'm trying to create a measure that will calculate divide sales by tota supplier revenue. This will give me a market share we have on that supplier for each here. See my schema below. 

 

carlenb_0-1695794295290.png

 

I also have the following graph where I've highlighted the end resultat to add a second line for % market share for eachsupplier. I seems like such an easy measure but I'm still a learner in this. 

carlenb_2-1695794564111.png

 

Any help on this would be amazing! 

 

 

1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

Hi @carlenb 

 

The data model is set up incorrectly

 

The 3 tables for supplier revenue, do they contain dates and have the same column names and format? In Power Query, I would append all these tables together into one table. I would append as new and untick the enable load on the 3 tables and load the new table into the report. Re create the relationship, but this time instead of having both directions,create a one to many from the Supplier Table to the New Table and Sales 20-22 table.

 

The same for the Calendar one to many to both other tables on the date columns. 

 

You can then create this measure to get the % Marketshare

 

% Marketshare =

VAR _SupRev = SUM(SupplierRevenue[Revenue])
VAR _SalesRev = SUM('Sales 2020-2022'[Total Value])

RETURN
DIVIDE(_SupRev, _SalesRev)

 

Convert to %

 

Use the Columns from the Date and Supplier tables in any visual or slicer and then the measure

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

 

 

View solution in original post

2 REPLIES 2
JoeBarry
Solution Sage
Solution Sage

Hi @carlenb 

 

The data model is set up incorrectly

 

The 3 tables for supplier revenue, do they contain dates and have the same column names and format? In Power Query, I would append all these tables together into one table. I would append as new and untick the enable load on the 3 tables and load the new table into the report. Re create the relationship, but this time instead of having both directions,create a one to many from the Supplier Table to the New Table and Sales 20-22 table.

 

The same for the Calendar one to many to both other tables on the date columns. 

 

You can then create this measure to get the % Marketshare

 

% Marketshare =

VAR _SupRev = SUM(SupplierRevenue[Revenue])
VAR _SalesRev = SUM('Sales 2020-2022'[Total Value])

RETURN
DIVIDE(_SupRev, _SalesRev)

 

Convert to %

 

Use the Columns from the Date and Supplier tables in any visual or slicer and then the measure

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

 

 

Thanks @JoeBarry - I got it to work! Thanks for a great explanation and suggestion on the model setup! 🙂 

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

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.

Top Solution Authors