Forum Discussion
Helper Imultiple 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 calculate column Terra Accuracy as below. However the total row of Terra Accuracy should be replaced with total of Weighted Accuracy column (Column 6) - which is Terra Accuracy calculated at material level * Weight . Im trying to use summarise to group all the rows for a material into one line and do the sumx calculation for columns D and E. I'm getting "multiple columns cannot be converted to a scalar value" error. Is my approach wrong?
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]))))
| History | Terra Forecast | Terra Accuracy | Weight | Weighted Accuracy | |
| MAX(0,IF(B2=0,0,1-(ABS(B2-C2)/B2))) | B/B$4 | D2*E2 | |||
| 20013772 | 7940 | 8244.18 | 96.2% | 0.971848 | 0.934616891 |
| 20013801 | 230 | 356.67 | 44.9% | 0.028152 | 0.012647491 |
| 8170 | 356.67 | 141.1% | 94.7% |
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
2 Replies
- TomMartens
Super User
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
SinduNThank 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]))