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.
Hello,
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.
Br,
Depa68
Solved! Go to Solution.
are you able to share some data?
effectively it should be qutie easy to calculate these values
example
gross profit =
VAR proda =
CALCULATE ( SUM ( table[sales] ), product = "product a" )
VAR prodb =
CALCULATE ( SUM ( table[sales] ), product = "product b" )
RETURN
DIVIDE ( proda, prodb, 0 )
Proud to be a Super User!
Hi,
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.
Kind Regards,
Hi, @Depa68
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.
Best Regards,
Community Support Team _ Janey
Hi,
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..... 😉)
Kind Regards
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 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |