Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SinduN
Helper I
Helper I

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 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 AccuracyWeightWeighted Accuracy
   MAX(0,IF(B2=0,0,1-(ABS(B2-C2)/B2)))B/B$4D2*E2
2001377279408244.1896.2%0.9718480.934616891
20013801230356.6744.9%0.0281520.012647491
 8170356.67141.1% 94.7%
1 ACCEPTED SOLUTION
TomMartens
Super User
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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.