Hi,
I wrote two metrics. One for summarising invoice values and currency exchange and the second to interact with the slicer. The problem that I have is that when I'll choose slicer options "Sales", "Direct Expences" or "Overheads", chartis showing correct information. When I select "Net Profit/Loss" or "Gross Profit/Loss", the chart is blank. There is no rocket science here so I don't know what went wrong. May I ask for your assistance?
Invoice value & currency exchange:
SQL_Actual_Adjusted =
var SQL_Values = sum('SQL_NLPostedNominalTran - Transactions'[GoodsValueInBaseCurrency])
var Region = selectedvalue('DIM_Regions and Logos'[Short Country Code])
var currency_opt = selectedvalue('DIM_Currency'[Currency])
var USD_GBP = average('DIM_Exchange USD / GBP'[USD/GBP])
VAR GBP_USD = average('DIM_Exchange GBP / USD'[GBP/USD])
var Meta = selectedvalue(DIM_Categories_PnL[Meta Category])
var Goods_Value = if(Meta="SALES",SQL_Values*(-1),SQL_Values)
var UK_GBP = calculate(Goods_Value,'SQL_NLPostedNominalTran - Transactions'[Region]="UK")
var UK_USD = calculate(Goods_Value,'SQL_NLPostedNominalTran - Transactions'[Region]="UK")*GBP_USD
var USA_GBP = calculate(Goods_Value,'SQL_NLPostedNominalTran - Transactions'[Region]="USA")*USD_GBP
var USA_USD = calculate(Goods_Value,'SQL_NLPostedNominalTran - Transactions'[Region]="USA")
return
SWITCH(
TRUE(),
Region = "UK",if(currency_opt = "GBP",UK_GBP,UK_USD),
Region = "USA",if(currency_opt = "GBP",USA_GBP,USA_USD))
the one which interacts with the slicer:
TIMESERIES SWITCH ACTUAL =
VAR OPTION = SELECTEDVALUE('DIM_Meta Cat Order'[Meta Category - Copy])
VAR SALES_V = CALCULATE([SQL_Actual_Adjusted],DIM_Categories_PnL[Meta Category]="SALES")
VAR DIRECT_EXP = CALCULATE([SQL_Actual_Adjusted],DIM_Categories_PnL[Meta Category]="DIRECT EXPENCES")
VAR OVERHEADS = CALCULATE([SQL_Actual_Adjusted],DIM_Categories_PnL[Meta Category]="OVERHEADS")
VAR NET_VAL = SALES_V-DIRECT_EXP-OVERHEADS
VAR GROSS_VAL = SALES_V-DIRECT_EXP
RETURN
SWITCH(
TRUE(),
OPTION = "SALES", SALES_V,
OPTION = "DIRECT EXPENCES",DIRECT_EXP,
OPTION = "OVERHEADS", OVERHEADS,
OPTION = "NET PROFIT/LOSS", NET_VAL,
OPTION = "GROSS PROFIT/LOSS", GROSS_VAL)
Kind regards,
Daniel
