Visual % of earning per Net Value on various granularities

[stanislav_dugas]

Hi guys,

I am trying to perform this calculation but i cant figure out how to make it dynamic to make sure it works across different levels of Granularity.

These is just an example of data. What i need to do is perform the calculation that will give me a % of earnings from Net result based on granularity selected in Visual.

Example1: If want to show visual % of earnings per ID so for ID 1 it will calculate 2/sum(2-1-0,5)

Example 2: If want to show visual % of earnings per Country so for Russia Country the calculation would be sum(2+2)/sum(-1-0,5-1-0,5) 

Is it possible to perform such a calculation?

Country	ID	Accounts	Value	% of earning from net result
Russia	1	Earnings	2
Russia	1	Costs	-1
Russia	1	Expenses	-0,5
Russia	2	Earnings	2
Russia	2	Costs	-1
Russia	2	Expenses	-0,5
Germany	3	Earnings	3
Germany	3	Costs	-2
Germany	3	Expenses	-0,5
Germany	4	Earnings	4
Germany	4	Costs	-2
Germany	4	Expenses	-1

Thank you for any help,

Stan

[stanislav_dugas]

The problem is that by pivoting the account column i would get soo many additional columns since i only showed here 3 accounts but in reality there is like 20 of them and i have over 4mil rows.

I want to divide net/value by earnings in a way that when i adjust the visual it will recalculate based the lowest level of granularity in that visual.

The expected result is that when i show visual of the % column based on ID granularity it will give me this.

ID	% of Net value
1	25%
2	25%
3	17%
4	25%

And then if i adjust the visual to show by country it wont sum up all the %tages by ID but rather do a calculation based on a country level.

Country	% of Net value
Russia	25%
Germany	21%

Hope its more clear now.

[Fowmy · Accepted solution]

@stanislav_dugas 

Create these three measures, you can also have a single measure

Earnings = CALCULATE(SUM(Table5[ Value ]),Table5[Accounts] = "Earnings")

Net Total = SUM(Table5[ Value ])

Visual % of Earning = 

DIVIDE(
    [Net Total],
    [Earnings]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[stanislav_dugas]

Thanks alot for help.

I realised what the issue was. I was using simmilar calculation as you posted but i was puting into New column rather through meassure functionality. Now i am reading that not every functions behaves same when its used in Column and in Meassure.

Do you know why in this case i dont get the same result? How would the function have to look like if i wanted to create a new column?

[Fowmy]

It will be easier if you pivot the Accounts column into columns so you will get like 

Country ID,  Earnings,  Costs,  Expenses,  Accounts

Your calculation is not very clear, you want to divide Earning by other  accounts? Share the expected results