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.
Dear All,
I have economic activities, for example, activity 1, ..., activity n. Further, activities contain X number of companies. Profit of the activity is formed by the sum of profits of companies that belongs to this activity. So currently I need to calculate the share of companies within the economic activity.
I am using the following measure:
Share company= 100*(DIVIDE((SUM(Powerbi[Profit])),CALCULATE(SUM(Powerbi[Profit]), Powerbi[Company], Powerbi[Economic activities])))
Unfortunately this calculating company shares from the total profit of all economic activities. So, total profit 5 000 000, while total profit for activity 1 is equal to 4 000 000. Therefore, the share of companies that belongs to activity 1 is calculating from 5 000 000 not 4 000 000. Where is my mistake?
Solved! Go to Solution.
Hi @Anonymous
I see your latest screenshot. Do you have two tables in your data model? There should be key columns like [Economic Activity] in both tables. Or you couldn't relate two tables in your calcualte.
Activity Table:
Company Table:
Measure:
Percentage =
VAR _Total = CALCULATE(SUM(Activity[Profit]),FILTER(Activity,Activity[Economic activities] = EARLIER(Company[Activity])))
RETURN
DIVIDE(Company[Profit],_Total)
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi manap
Click here to download a solution
Please leave kudos if it solves your problem
It is best practive to breakdown complex DAX measure into easy chunks that you can test and reuse ....
I have created 3 measures and used a % format rather than multiply by 100
Profits =
-- get the profits
SUM(Sales[Profit])
Profit for all companies =
-- get profit for all companies within the current context.
-- The ALL command with keep the row Activity context but changes the row Company context to all.
CALCULATE(
[Profits],
ALL(Sales[Company])
)
% profit of all activity =
-- get the % of profits for all the activity
DIVIDE( [Profits] , [Profit for all companies] )
I have tried your method, but it resulted 1 for all companies
Hi @Anonymous
I see your latest screenshot. Do you have two tables in your data model? There should be key columns like [Economic Activity] in both tables. Or you couldn't relate two tables in your calcualte.
Activity Table:
Company Table:
Measure:
Percentage =
VAR _Total = CALCULATE(SUM(Activity[Profit]),FILTER(Activity,Activity[Economic activities] = EARLIER(Company[Activity])))
RETURN
DIVIDE(Company[Profit],_Total)
Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please provide sample data or a PBIX file and a depiction of the expected outcome
Proud to be a Super User!
Paul on Linkedin.
Here is a screenshort
We are going to need sample data of the actual structure of the tables. The depiction you have provided does not allow to establish a relationship between the two tables shown.
Proud to be a Super User!
Paul on Linkedin.
What about this
@Anonymous try this:
Share company=var CurrentActivity=Powerbi[Economic activities]
return DIVIDE(SUM(Powerbi[Profit]),SUMX(FILTER(Powerbi,Powerbi[Economic activities]=CurrentActivity),Powerbi[Profit]))*100
how to create this var CurrentActivity=Powerbi[Economic activities] if I have a column.
@Anonymous wrap the column into VALUES(), that is:
var CurrentActivity=VALUES(Powerbi[Economic activities])
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |