cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Depa68
Frequent Visitor

Calculate ratios within items in a matrix

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

Depa68_0-1627175721384.png

 

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

 

Depa68_1-1627175767764.png

 

 

Could you please suggest a viable as simple as possible solution with DAX ? 

 

Thanks in advance.

Br,

Depa68

1 ACCEPTED SOLUTION

@Depa68   If the problem is solved, you can mark your answer.

View solution in original post

5 REPLIES 5
vanessafvg
Super User I
Super User I

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 )





Did I answer your question? Mark my post as a solution!

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:

 

 

Depa68_0-1627219321969.png

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

Depa68_1-1627219591497.png

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 :

Depa68_0-1627419929889.png

 

 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

@Depa68   If the problem is solved, you can mark your answer.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors