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

Question

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

[Edit] your question to show a sample of your source data, and the expected output from the measure. — Olly, Apr 30 '19 at 07:03
hi .this is impossible for me to put source data because of huge size.. and when i use code which i mentioned in question this shows nothing but an error — rebma testio, Apr 30 '19 at 07:05
A **sample** of data, to show the structure and some representative rows... — Olly, Apr 30 '19 at 07:06
i know what is mean by sample data. i said this is not possible because of large number of rows.. — rebma testio, Apr 30 '19 at 07:10
It seems likely that you need some sort of iteration (probably a SUMX) to perform the `IF` statement for each row of _some_ table. With no understanding of your source data structure though, it's impossible to give a meaningful answer. — Olly, Apr 30 '19 at 07:35
ok here is the link of my data.. https://www.dropbox.com/s/tr3epqtetf36hfl/abc.xlsx?dl=0 — rebma testio, Apr 30 '19 at 08:03
Your measure refers to multiple tables... Which table is that? What are you actually trying to achieve with your measure? Please, read [ask], and clarify your actual question. It's very difficult to help you, without all the relevant information. — Olly, Apr 30 '19 at 08:12
and here is the link of tables .. https://www.dropbox.com/s/b3hsphvttc2c473/Capture.PNG?dl=0 — rebma testio, Apr 30 '19 at 08:17
It's still really not clear what you actually want. Are you trying to multiply USD values by 139.2? Or only return a sum of values where currency is USD? What is the relationship between the tables, and how does this relate to the sample data you provided? The measure itself should be relatively simple, but your definition needs to be clearer. — Olly, Apr 30 '19 at 08:33
hello... its simple i think i only want to sum those if currency is USD with filters which i mentioned and in one condition i want to multiply by 139.2 and on the other condition not.. — rebma testio, Apr 30 '19 at 09:43
if you have know how about qilk sense expression then kindly check update question where i mentioned qlik sense expression .. — rebma testio, Apr 30 '19 at 09:44

Olly · Accepted Answer · 2019-04-30T12:23:37.317

1

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/

edited Apr 30 '19 at 12:23

answered Apr 30 '19 at 09:47

Olly

7,749
1
19
38

hi in last line.. BillingDocument[Billing Type_FKART] = "ZG2" || BillingDocument[Billing Type_FKART] = "ZL2 i want to operate as "not equal to " sign .. when i replace = to <> this shows an error – rebma testio Apr 30 '19 at 10:18
hi i already did that one as i menitoned in comment but this shows an error. and when i replace <> to = this then no error but when i replace = to <> then this shows an error .... and i want <> operator not = in last condition .. – rebma testio Apr 30 '19 at 10:27
Edited answer again, to make the filter criteria clearer. – Olly Apr 30 '19 at 10:35
check table structure please which i edit in question – rebma testio Apr 30 '19 at 10:52
I'm doing ALL the work, here... How about you mock up a PBIX file with the table structure and relationships you're working with, and some sample data? – Olly Apr 30 '19 at 10:59
kindky check update question .. i attached pbi file.. in that file i use = instead of <> so this shows an error .. i used your previous code kindly check and please modify ... @Olly – rebma testio Apr 30 '19 at 11:37
have you checked.. kindly check if you not – rebma testio Apr 30 '19 at 12:03
I have completely updated my answer. This would have been SO MUCH QUICKER and easier if you'd posted a clear requirement in the original question. – Olly Apr 30 '19 at 12:24
one more requirement please.. what about BN , MN like figure is too long so want BN or MN or K keyword at the end – rebma testio Apr 30 '19 at 12:34
1

That's a different question - and I'm sure Google can help you. – Olly Apr 30 '19 at 12:35
ok if i did not found then i will post question .. thanku for cooperating and endurance me :) – rebma testio Apr 30 '19 at 12:51
please check this thread.. https://stackoverflow.com/questions/55946522/expression-in-power-bi-and-qliksense – rebma testio May 02 '19 at 08:36

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

1 Answers1