Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm very new to Power BI, but I do have a development background. I am trying to learn how to create a profitability report for our organiztion. This report needs to be dynamic enough to look back at data from Dynamics CRM and determine what the total income, profit, and profit margin are for an employee. I am importing data from Dynamics CRM through OData.
I have imported data for the last year into Power BI. I can find total income by month without any issue, but when it comes to calculating profit, I'm stuck trying to calculate by the selected date range. My calculation is basically Total Income - Total Employee Cost = Profit where Total Employee Cost = Salary + Bonus + Overhead. That cost divided by 12 gives me my monthly cost, but if a user is trying to report on 3 months, I'm having trouble figuring out how to take the Monthly Cost and multiply it by the number of months selected.
Solved! Go to Solution.
I may be completely off target but why not use the measures you already have and use your months as the axis or as rows to show the relevant data by specific month. Using a date calendar you could also show by calendar quarter. In other words, let your measures do the simple part of the equation (the math) and have PowerBI divide it up into periods in the visualizations using slicers and filters.
Of course it has been a long week and I may be completely misunderstanding the question.
Proud to be a Super User!
I may be completely off target but why not use the measures you already have and use your months as the axis or as rows to show the relevant data by specific month. Using a date calendar you could also show by calendar quarter. In other words, let your measures do the simple part of the equation (the math) and have PowerBI divide it up into periods in the visualizations using slicers and filters.
Of course it has been a long week and I may be completely misunderstanding the question.
Proud to be a Super User!
Hi @kcantor ,
The data is not matching until and unless the slicer end date is current date. But I need slicer end date as required and the data should match .
Can you please help on this
Thanks in advance.
Hello,
I've tried this approach of creating a measure using DATESBETWEEN Function, But this is not supported in DirectQuery mode.
I'm am presently working with DirectQuery mode. Do you have any alternative method for the above question?
Can someone please help me with this?
Thanks
Without knowing the exact formula or the granularity of the data is difficult to help you better, but you can try to divide the Total Income by a dynamic number of months like DIVIDE(Total Profit ; COUNTROWS(VALUES(Dates[Month]))) or something similar.
Hi, i am learner of power Bi how to calculate profit using dax expression
Close to that.
I have a measure for TotalIncome.
=SUM('Opportunities'[Fee])
I have a measure for TotalEmployeeCost
='Employees'[MonthlyCost] * <insert code here for selected months >
I have a measure for TotalProfit
= [TotalIncome] - [TotalEmployeeCost]
TotalEmployeeCost is a fixed value so I need to multiply it by the number of selected months in the report view.
I know this is probably not super helpful but I believe what you need is to wrap your Income and Cost equations in a Calculate function and utilize a time intelligence function as the filter. You can find a listing of the time intelligence functions here: https://msdn.microsoft.com/en-us/library/ee634763.aspx.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |