Hi All,
I am having SalesData of Q4 for 2 years in which I am facing problem with 3 columns ie.
a)RevenueInvoiced
b)ProfitInvoiced and
c)ProfitMarginInvoiced
Actually,when I analyzed the Excel I found the formula used to calculate is:
But,I have a doubt here :
Some of the rows are having ProfitInvoiced=0 & RevenueInvoiced=0 or some other combinations in which no result is mentioned in ProfitMarginInvoiced column
How should I proceed to visualize ProfitMarginInvoiced if the requirement is to show Top10 Products/Manufacturers ?
As per my understanding ProfitMargin plays a key role in finding out TOPN in respect of Manufacturers or ProductsSold.
I am attaching the sample file with 99 rows of data of Q4 for year 2020 ProfitInvoiced.xlsx (18.3 KB)
I have several questions, but to start having a PBI file to work with will be essential to help you fully.
I’m not sure I understand the data you’ve shared. As a Financial BI Analyst, I wouldn’t describe profit margin as “invoiced”. Gross profit margin is just a calculation based on net revenue and cost of goods sold. Specifically, ((Revenue - COGS)/Revenue) * 100.
Net Profit Margin would be (Net Income/Revenue)
I’m again not sure if this is a currency exchange issue, but profit margins don’t go over 100% - certainly not over 300%. That would somehow require your cost of goods sold to be negative. I can think of a select few cases where that might happen with some accounting tricks, but I don’t think that’s what you’re after.
A PBI file with the underlying data or an explanation of how the “profit margin invoiced” is being calculated will be extremely helpful.
Thanks for your response,
Sorry,I was outside so could not check your reply.
But,I have only this much of information with me and its mentioned that I can think in every possible cases with this data.
So,I cannot clarify the doubts.
But, again thanks for your response and with this information only if you can suggest something to me it will be good .
Without going into details about data correctness, if what you are trying to achieve is margin % calculated as profit invoiced/ revenue invoiced, you could use measure like this
Blockquote
Profit margin =
VAR _filteredTable =
FILTER (
ADDCOLUMNS (
Sheet1,
– flag column to remove rows that have either revenue or profit blank or 0
“ProtiRevenueMultiple”, Sheet1[Profit Invoiced] * Sheet1[Revenue Invoiced],
“_Profit”, SUM ( Sheet1[Profit Invoiced] ),
“_Revenue”, SUM ( Sheet1[Revenue Invoiced] )
),
ABS ( [ProtiRevenueMultiple] ) > 0
)
VAR _totalProfit =
SUMX ( _filteredTable, [_Profit] )
VAR _totalRevenue =
SUMX ( _filteredTable, [_Revenue] )
RETURN
DIVIDE ( _totalProfit, _totalRevenue )
Blockquote
What it will do is to filter out all rows that have either profit or revenue equal to blank or 0, on the rest it will calculate your profit margin.
Then you can apply Top N filter in your visual to get just the highest margin. I would assume those would be shown in a separate visuals. In the end it would look something like this
