cancel
Showing results for
Did you mean:
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

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 ?

Br,

Depa68

1 ACCEPTED SOLUTION
Community Support

5 REPLIES 5
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 )

Proud to be a Super User!

Frequent Visitor

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,

Community Support

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.

Best Regards,

Community Support Team _ Janey

Frequent Visitor

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

Community Support

Announcements

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

#### Microsoft named a Leader in The Forrester Wave

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

#### 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 Solution Authors
Top Kudoed Authors