How to calculate % from single column

[sur27078]

I'm currently working with a General Ledger (GL) dataset that includes only the following columns: Year, GL Account, Cost Center, Month, and Balance.

I’ve created a visual card that filters data based on GL accounts. For example, to calculate Gross Profit, I filter for GL Revenue and GL Expense accounts.

Now, I want to calculate the Gross Margin percentage, which is defined as:

Gross Margin % = Gross Profit / Total Revenue

My question is:
How can I calculate a percentage when I only have a single column for Balance?

can I calculate based on 2 visual cards?

Thanks,

Sur

[sur27078]

@GeraldGEmerickIt seems to be working, but how can I filter by month and cost center?

[GeraldGEmerick]

@sur27078 You could use ALLSELECTED instead of ALL like so perhaps:

Gross Margin % =
VAR _TotalRevenue = CALCULATE( SUM( 'Table'[Balance]), ALLSELECTED( 'Table' ) )
VAR _GrossProfit = CALCULATE( SUM( 'Table'[Balance] ), FILTER( ALLSELECTED( 'Table' ), [GL_Account] IN { "5014", "4508" } ) )
VAR _Return = DIVIDE( _GrossProfit, _TotalRevenue, 0 )
RETURN _Return

[sur27078]

@GeraldGEmerick THANK YOU SO MUCH. it's working.

[GeraldGEmerick]

@sur27078 Very glad to hear!

[GeraldGEmerick · Accepted solution]

@sur27078 You should be able to do something like the following:

Gross Margin % =
VAR _TotalRevenue = CALCULATE( SUM( 'Table'[Balance]), ALL( 'Table' ) )
VAR _GrossProfit = CALCULATE( SUM( 'Table'[Balance] ), FILTER( ALL( 'Table' ), [GL_Account] IN { "5014", "4508" } ) )
VAR _Return = DIVIDE( _GrossProfit, _TotalRevenue, 0 )
RETURN _Return

You would need to modify this for whatever your GL_Account codes are for however you are filtering your Revenue and Gross Profit card visuals.