Forum Discussion

Wangfl's avatar
Wangfl
Frequent Visitor
4 years ago
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...
Need Help
  • PaulDBrown's avatar
    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 )

     

     

Greg_Deckler's avatar
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's avatar
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's avatar
    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 )

     

     

    • Wangfl's avatar
      Wangfl
      Frequent Visitor
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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.