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.
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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
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
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
Maybe a screenshot of your table could help
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |