Forum Discussion

SinduN's avatar
SinduN
Icon for Helper I rankHelper I
7 years ago
Solved

multiple columns cannot be converted to a scalar value for power bi on Direct Query

I have a power BI built on direct query on SSAS model.  I have the data at customer group material level.  I want to summerise the data to get at the material level and calculate.  Im able to calcu...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    I have to admit that I do not understand what the formula is doing exactly, but by analyzing the parenthesis it seems that the false part of the first IF is a SUMMARIZE ...

    Terra Accuracy =
VAR __History = SUM('V_FORECASTACCURACY'[DeliveryQty in SU])
VAR __Terraforecast = SUM('V_FORECASTACCURACY'[TRR-Forecast Qty])
RETURN
if(
	HASONEFILTER(US_MATERIAL[GTIN])
	,iF( NOT ISBLANK(__history)
		,if(__History<>0
			,if((1-DIVIDE(abs( __history-__Terraforecast), __History))>0
				,(1-DIVIDE(abs( __history-__Terraforecast), __History))			
	)))
	,SUMMARIZE(
		V_FORECASTACCURACY
		,US_MATERIAL[Material Text]
		,"acc",
			1-DIVIDE(abs( __history-__Terraforecast), __History)*__History/sum(V_FORECASTACCURACY[DeliveryQty in SU])
			/CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]),ALLEXCEPT(US_MATERIAL,US_MATERIAL[Product Line])))
)

    The SUMMARIZE returns two columns [Material Text] and [acc]. A table with more than one column and more than one row cannot be converted implicitly into a scalar value.

    Maybe you need to iterate over the table returned by a SUMX(SUMMARIZE(...), [acc]), but this is just guesswork.

     

    Hopefully, this gets you started.

     

    Regards,

    Tom

     

     

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
7 years ago

Hey,

 

I have to admit that I do not understand what the formula is doing exactly, but by analyzing the parenthesis it seems that the false part of the first IF is a SUMMARIZE ...

Terra Accuracy =
VAR __History = SUM('V_FORECASTACCURACY'[DeliveryQty in SU])
VAR __Terraforecast = SUM('V_FORECASTACCURACY'[TRR-Forecast Qty])
RETURN
if(
	HASONEFILTER(US_MATERIAL[GTIN])
	,iF( NOT ISBLANK(__history)
		,if(__History<>0
			,if((1-DIVIDE(abs( __history-__Terraforecast), __History))>0
				,(1-DIVIDE(abs( __history-__Terraforecast), __History))			
	)))
	,SUMMARIZE(
		V_FORECASTACCURACY
		,US_MATERIAL[Material Text]
		,"acc",
			1-DIVIDE(abs( __history-__Terraforecast), __History)*__History/sum(V_FORECASTACCURACY[DeliveryQty in SU])
			/CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]),ALLEXCEPT(US_MATERIAL,US_MATERIAL[Product Line])))
)

The SUMMARIZE returns two columns [Material Text] and [acc]. A table with more than one column and more than one row cannot be converted implicitly into a scalar value.

Maybe you need to iterate over the table returned by a SUMX(SUMMARIZE(...), [acc]), but this is just guesswork.

 

Hopefully, this gets you started.

 

Regards,

Tom

 

 

  • SinduN's avatar
    SinduN
    Icon for Helper I rankHelper I
    7 years ago

    Thank you Tom. I had to sumx for the summarize section. Issue was that the summarize was giving a table of the measure"acc". using Sumx of the measure "acc" fixed the issue. Instead if using one big formula - i broke it into 2 measures and used teh dame concept and it worked.

    test =
    VAR __History = SUM('V_FORECASTACCURACY'[DeliveryQty in SU])
    VAR __Terraforecast = SUM('V_FORECASTACCURACY'[TRR-Forecast Qty])
    RETURN
    if(
    HASONEFILTER(US_MATERIAL[GTIN])
    ,iF( NOT ISBLANK(__history)
    ,if(__History<>0
    ,if((1-DIVIDE(abs( __history-__Terraforecast), __History))>0
    ,(1-DIVIDE(abs( __history-__Terraforecast), __History))
    )))
    ,sumx(SUMMARIZE(
    V_FORECASTACCURACY
    ,US_MATERIAL[Material Text]
    ,"Acc",
    (1-DIVIDE(abs( __history-__Terraforecast), __History)*__History/sum(V_FORECASTACCURACY[DeliveryQty in SU])
    /CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]),ALLEXCEPT(US_MATERIAL,US_MATERIAL[Product Line])))),[Acc]))