Simple Cost Ratio from GL Data

[Anonymous]

Hello all, pretty frustrated with what I feel should be a simple task

I have a simple set of GL data, as seen below, where my amount is in one column, and my accounts are rows.

I then do a very simple model where I have a GL Account class lookup table, which assigns the class "revenue" and "cost" to each account.

I then just want to have a simple cost ratio at the account level, of cost/total revenue. the table should look as such below, but nothing returns at the account level for a margin. even the revenue accounts are wrong, as Revenue Reg should be 60% of total revenue, , and Revenue Spec should be 40%

My measures are 

Value = SUM(data_table[Amount])

Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue")

Cost Margin = DIVIDE([Value],[Total Revenue])

any help would be greatly appreciated

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please have  a try.

Create a measure.

Total Revenue = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT(gl_class,gl_class[Class ]))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue",all(gl_class[Account]))