Forum Discussion
Solved
Switch + Selectedvalue return blank
Hello everyone, I'm struggling to solve a problem with a measure. I am using Switch + Selectedvalue to build a customized P&L report in Power BI, but two of lines "Other COGS" and "Ratio 1 retur...
If you are performing a division, you are going to need to remove the row filters. Something along the lines of:
Ratio = VAR _Profit = CALCULATE ( [Gross Profit], ALL ( 'P&L Structure' )) VAR _NetSales = CALCULATE ( [Net Sales], ALL ( 'P&L Structure' )) RETURN DIVIDE ( _Profit, _NetSales )
hi Greg,
Thank you for fast response. however, the measure [Other COGS] and [Ratio 1]that I refer to seems is only one value. But if I change formula into constant number. it will shows up! Could you help me understand what is going on? ğŸ˜
If you are performing a division, you are going to need to remove the row filters. Something along the lines of:
Ratio =
VAR _Profit =
CALCULATE ( [Gross Profit], ALL ( 'P&L Structure' ))
VAR _NetSales =
CALCULATE ( [Net Sales], ALL ( 'P&L Structure' ))
RETURN
DIVIDE ( _Profit, _NetSales )
Hii,
Thank you so much!! Its working now.
Could help me do the last favor? 🙂
Is a way to make this formula more short and clean?
Other COGS =VAR NetSales =CALCULATE ( [Net Sales], ALL ( 'P&L Structure' ) )VAR GrossProfit =CALCULATE ( [Gross Profit], ALL ( 'P&L Structure' ) )VAR StandardCost =CALCULATE ( [Standard Cost], ALL ( 'P&L Structure' ) )VAR TotalSupplyChainVariances =CALCULATE ( [Total Supply Chain Variances], ALL ( 'P&L Structure' ) )VAR LIFOFIFO =CALCULATE ( [LIFO / FIFO / Inventory Adjustments], ALL ( 'P&L Structure' ) )VAR DirectCharges =CALCULATE ( [Direct Charges], ALL ( 'P&L Structure' ) )VAR TotalFreightExpense =CALCULATE ( [Total Freight Expense], ALL ( 'P&L Structure' ) )VAR COGSAdmin =CALCULATE ( [COGS Admin], ALL ( 'P&L Structure' ) )RETURNNetSales-GrossProfit-StandardCost-TotalFreightExpense-LIFOFIFO-DirectCharges-TotalFreightExpense-COGSAdminIt looks pretty succinct to me....Not sure you can do much more there...
Lol.. Got it!
Thank you again~😀