Forum Discussion
How to calculate % from single column
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 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 _ReturnYou would need to modify this for whatever your GL_Account codes are for however you are filtering your Revenue and Gross Profit card visuals.
5 Replies
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 _ReturnYou would need to modify this for whatever your GL_Account codes are for however you are filtering your Revenue and Gross Profit card visuals.
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 _ReturnGeraldGEmerick THANK YOU SO MUCH. it's working.
