Calculate ratios within items in a matrix

[Depa68]

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

[vanessafvg]

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 )

[Depa68]

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,

[v-janeyg-msft]

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

[Depa68]

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

[v-janeyg-msft · Accepted solution]

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