The percentage calculating incorrectly

[Anonymous]

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?

[speedramps]

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] )

[Anonymous]

I have tried your method, but it resulted 1 for all companies

[Anonymous · Accepted 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.

[PaulDBrown]

Please provide sample data or a PBIX file and a depiction of the expected outcome 

[Anonymous]

Here is a screenshort 

[PaulDBrown]

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.

[Anonymous]

What about this

[Anonymous]

@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

[Anonymous]

how to create this var CurrentActivity=Powerbi[Economic activities] if I have a column.

[Anonymous]

@Anonymous wrap the column into VALUES(), that is:

var CurrentActivity=VALUES(Powerbi[Economic activities])