Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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.
Any help on this would be amazing!
Solved! Go to Solution.
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
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! 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |