variablespowerbipowerbi-desktopkpi

A table of multiple values was supplied where a single value was expected


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

https://www.dropbox.com/s/flondhallo08j98/test4.pbix?dl=0


Solution

  • 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/