How do I calculate a profit ratio from 2 rows in a matrix table ?

[MarkPendragon]

Hi, 

I have a matrix table with a column which lists the accounting lines and a column(s) for the values by month.

How do I calculate a ratio. Ie Gross per Unit sold for example ?

I have searched for guidance on DIVIDE, RELATED, SUMX etc but am lost.

In simple terms   DIVIDE([Accounts Line]  where [Accounts Line]="Sales", [Accounts Line] where [Accounts Line]="Units Sold"

I am sure this is so simple, but I am struggling to find the correct syntax. It's just one table. So no cross table linking required.

Thanks

Mark

[kcantor · Accepted solution]

@MarkPendragon

I would suggest writing helper measures. 

Calculate your sales lines using CALCULATE with a [Account Line] = "Sales" and name the measure Sales. Calculate your Units Sold lines the same way and call that Units. Then do a divide with the measures.

=DIVIDE([Sales],[Units])

On the up side of this you can always find the source of issues and it is easier to troubleshoot the simple measures as oppsed to complex calculations with all of the logic built in.

[MarkPendragon]

Here is the table, I have removed the financial month values. Seeing as I have an index column, in simple terms..... where table is Index1. if value = 1 / if value = 15

[GeraSanz11]

I don't know exaclty if this is what you want but you could try a new Meassure with 'Calculate SUM' as follows

Ratio = (Calculate ( SUM(TableName[FinancialMonthValueColumnName]), Filter(TableName, TableName[Index1] =1)
             /  

(Calculate ( SUM(TableName[FinancialMonthValueColumnName]), Filter(TableName, TableName[Index1] =15)

Just change table names with yours

Hope this helps 

[MarkPendragon]

Hi,

Thank you for the formula, it certainly calculates as I expected. Ideally I wanted it in the same table, but realised I have to create another matrix with just that measure in and the same column headings.

Appreciate your guidance.

Thanks

Mark

[GeraSanz11]

Maybe a screenshot of your table could help