I am trying to create KPI and for that i try to create a variable
i have this formula
Total_Sales_Variable =
IF (
VALUES (BillingDocument_Header[Billing Doc. currency_WAK] ) = "USD",
139.2,
1
) *
CALCULATE (
SUM (BillingDocument_Item[Sales Amount_NEWR]),
FILTER (
'Invoice_Calender',
'Invoice_Calender'[Fiscal_Year] = 2016 &&
'Invoice_Calender'[Fiscal_Year] = 2017
),
FILTER (
'BillingDocument_Header',
'BillingDocument_Header'[Bill Type_FKRT] <> "Z2" &&
'BillingDocument_Header'[Bill Type_FKRT] <> "Z2"
)
)
when i add this shows an error
MdxScript(Model) (8, 5) Calculation error in measure 'Invoice_Calender'[Total_Sales_Variable]: A table of multiple values was supplied where a single value was expected.
and i am trying of achieve if Billing Doc. currency_WAk is USD then i want to sum of this field Sales Amount_NEWR against filters Fiscal_Year = {'2016','2017'} and [Billing Type_FKART] ={'ZG2','ZL2'}
this is what i want to achieve in power bi i want to convert this into dax expression power bi qlik sense expression
if([Billing Doc. currency_WAERK] = 'USD',
sum({< Fiscal_Year = {'2016','2017'},[Billing Type_FKART] -={'ZG2','ZL2'}>}[Billing Sales Amount_NETWR])*139.2,
sum({< Fiscal_Year = {'2016','2017'},[Billing Type_FKART] -={'ZG2','ZL2'}>}[Billing Sales Amount_NETWR]))
any help ?
UPDATE ATTACHED FILE
kindly check pbix file with data and tables
Edited: Now we finally have clarity on your table structure and relationships, and understand your required measure!
Total Sales Variable =
CALCULATE (
SUMX (
VALUES ( test_billing_doc_header[Billing Doc. currency_WAERK] ),
IF (
test_billing_doc_header[Billing Doc. currency_WAERK] = "USD",
139.2,
1
) * SUM ( test_biilingg_doc_item[Billing Sales Amount_NETWR] )
),
Invoice_Calender[Fiscal_Year] = 2016 || Invoice_Calender[Fiscal_Year] = 2017,
test_billing_doc_header[Billing Type_FKART] <> "ZG2" && test_billing_doc_header[Billing Type_FKART] <> "ZL2"
)
PBIX file: https://pwrbi.com/so_55914086-3/