Switch + Selectedvalue return blank

[Wangfl]

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!

[Greg_Deckler]

@Wangfl Remember that SELECTEDVALUE will return blank by default if it finds two or more different values in the rows it is analyzing.

[Wangfl]

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? 😭

[Greg_Deckler]

@Wangfl Well, not sure all of the information is present to make a determination of what is going on. Also, I misread this initially. So, what are the formulas for your Ratio 1 and Other COGs measures? It is possible that in the context of the table visual that either the relationship is not matching those up or the context is changed such that the measures don't work as expected. If the measures are referencing the EPM table then pick a column in that table and do a count to get a row count in the table visual you are using. If the row count for those two come back zero or blank, then something is not matching up.

[PaulDBrown · Accepted solution]

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 )

[Wangfl]

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' ) )

RETURN

NetSales-GrossProfit-StandardCost-TotalFreightExpense-LIFOFIFO-DirectCharges-TotalFreightExpense-COGSAdmin

[PaulDBrown]

It looks pretty succinct to me....Not sure you can do much more there...

[Wangfl]

Lol.. Got it!

Thank you again~😀

[Wangfl]

@Greg_Deckler 

[PaulDBrown]

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]

Hi Thank you for fast response.

however, it seems like the column quality is all good..

[PaulDBrown]

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