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.
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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?
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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |