I'm facing issues, as I cannot find a solution with DAX, in calculating 3 ratios for items reported in following matrix:
I'd like to calculate, for each values (Actual, E9_21, E9_22 which are forecast and PY) following ratios:
Gross Profit - product A / Revenue prod A
Gross Profit - product B / Revenue prod B
Operating Income / Total Revenue
In the query I just was able to create the order sequence, while second query named Report Bi input is the outcome of a power pivot data model that I'm using as a source (as importing power pivot data model with measures failed):
Could you please suggest a viable as simple as possible solution with DAX ?
Thanks in advance.
Solved! Go to Solution.
are you able to share some data?
effectively it should be qutie easy to calculate these values
gross profit =
VAR proda =
CALCULATE ( SUM ( table[sales] ), product = "product a" )
VAR prodb =
CALCULATE ( SUM ( table[sales] ), product = "product b" )
DIVIDE ( proda, prodb, 0 )
sorry I'm a beginner in the community too: I'd like to attach my .pbix file but it seems not possible.
At least I am able to share here following status of my trial about "copy/pasting" formula you suggested:
It seems we're closer but I still miss something: could you please comment further ?
Please note that, if possible, my idea would be to get ratio calculation for each column of the matrix,
which is currently representing values for Items in different scenarios (actual/forecast/previous year).
If it would be possible to share my file, please clarify me which is the applicable procedure.
You should try calculate function like this: calculate(sum(table[column1]),[column2]="name")
You seem very new to dax, but I can’t understand what you want to calculate. If possible, please explain your calculation logic in detail and your expected output. You can upload the sample file to onedrive for business and copy the anonymous link.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Community Support Team _ Janey
basically I wanted to land to get a matrix with ratio % measures in rows and get a delta across periods in columns.
Following additional support at office, they installed me Tabular Editor as a power bi External Tool and teached me some dax code about above topic.
Basically I had to create additional tables in powerBi through Tabular Editor, in order to get additional fields (being them linked to imported tables from power pivot) needed to calculate ratios.
Now I would say it's done :
Here following a link to tabular editor info: https://www.sqlbi.com/calculation-groups/
(it's a quite nice learning by doing process..... 😉)
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.