Expression in power bi and qliksense

2019-08-19 01:27发布

问题:

I have 2 files one is in qliksense and other is in power bi .. both have same tables and relationships..

qliksense expression is

if([Billing Doc. currency_WAERK] = 'USD',
sum({<[Billing Type_FKART] -={'ZG2','ZL2'}>}[Billing Sales Amount_NETWR])*139.2,
sum({<[Billing Type_FKART] -={'ZG2','ZL2'}>}[Billing Sales Amount_NETWR]))

and

power bi expression is

UPDATED

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] )
    ),
    test_billing_doc_header[Billing Type_FKART] <> "ZG2" && test_billing_doc_header[Billing Type_FKART] <> "ZL2"
)

now the problem is qliksense expression show value 26,599,175,877.32 and power bi shows 6,800,260,015,222,45

and i want qliksense value in power bi ..

this is the link for power bi file .. https://www.dropbox.com/s/i8aazcb19zt50l5/test4.pbix?dl=0

and this is for qliksense https://www.dropbox.com/s/jltgdls5rbintn4/test.qvf?dl=0

any help ?

回答1:

  1. Qliksense expression is correct but in table with [Fiscal_Year] field there is no data (calendar is not calculated in loading script). I've changed it to Invoice_year which is not missing and result is:
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]))
  1. I see also that there are difference in data as currency in PowerBi is already converted and in QlikSense not. Please use straight tables and pivot tables to analyze your data so it will be easier for you:

Power BI table (without any conversions - just simple sum and filter to select Years and FKART):

QlikSense table (with set analysis and if result is the same as in Power Bi without currency conversion):