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 return blank.

Financial Value =

SWITCH(

SELECTEDVALUE('P&L Structure'[Line Item Description]),

"Net Sales",[Net Sales],

"Standard Cost",[Standard Cost],

"Total Supply Chain Variances",[Total Supply Chain Variances],

"LIFO / FIFO / Inventory Adjustments",[LIFO / FIFO / Inventory Adjustments],

"Direct Charges",[Direct Charges],

"Total Freight Expense",[Total Freight Expense],

"COGS Admin",[COGS Admin],

"Other COGS",[Other COGS],

"Gross Profit",[Gross Profit],

"Ratio 1",[Ratio 1]

)

Here is my data model and formula. Could anyone help me check? Thank you in advance!

Need Help

PaulDBrown

4 years ago

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 )

11 Replies

PaulDBrown
Community Champion
4 years ago
It may be because there is a rogue blank in the field value. Try performing a "Trim" (and maybe "Clean") on the column in Power Query.

or even simpler, instead of referencig the name (value) in SELECTEDVALUE, use the Line Item Number instead
- Wangfl
  Frequent Visitor
  4 years ago
  Hi Thank you for fast response.
  
  however, it seems like the column quality is all good..
  - PaulDBrown
    Community Champion
    4 years ago
    Just for future reference, I actually meant (though my wording was indeed confusing) that one of the actual values may contain a blank at the end of the word/string - not a blank as an actual value. If you perform a Trim on the column, it cleans up any blank (character) at the end of a string
Greg_Deckler
Community Champion
4 years ago
Wangfl Remember that SELECTEDVALUE will return blank by default if it finds two or more different values in the rows it is analyzing.
- Wangfl
  Frequent Visitor
  4 years ago
  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? 😭
  - PaulDBrown
    Community Champion
    4 years ago
    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 )