Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

PowerBI - "Original" currency display not working, other currency fine

Upon refreshing my PowerBI model I get the following error message
Error Message:
MdxScript(Model) (89, 187) Calculation error in measure 'Report'[Equity Value]: A table of multiple values was supplied where a single value was expected.
When I look at specific currencies there is no problem.

Comments

  • Hi,

    It appears that you have quotes for 3 symbols that are not defined in the symbol table. But you need to find these symbols. In a few hours I will go on vacation, so I do not have more time to investigate.
    So find quotes for symbols that are not defined and either remove quotes or add symbols to Symbol table.
    I coded on purpose to fail refresh, but my attempt was to prevent refresh when there are duplicate quotes. As a side effect, when there are undefined symbols, then refresh fails too.

    Simple fix is also to change formula.

    In the release Report[Equity Value] formula is:
    Equity Value = ROUND(SUMX(VALUES(Symbol[Symbol])
    			, ROUND(
    					CALCULATE(SUM(Trans[QtyChange])
    					, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
    					, ALL(TransType), TransType[QtySign]<>0
    					)
    				,3)
    	
    			  * IF(ISFILTERED(SymbolSector[Sector]) || ISFILTERED(SymbolSector[Sensitivity]), CALCULATE(SUM(SymbolSector[Percent])), 1)
    			  * CALCULATE(
    					IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), VALUES(Quotes[Close])
    						, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, VALUES(Quotes[Price1]), 2, VALUES(Quotes[Price2]), 3, VALUES(Quotes[Price3]), 4, VALUES(Quotes[Price4]), 5, VALUES(Quotes[Price5]), VALUES(Quotes[Close]) )
    					)
    					, LASTNONBLANK(
    						DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), MAX(Dates[Date]))
    					  , CALCULATE(COUNT(Quotes[Close]) )) 
    				)
    		)
    , 2)
    I changed that to:
    Equity Value = ROUND(SUMX(VALUES(Symbol[Symbol])
    			, ROUND(
    					CALCULATE(SUM(Trans[QtyChange])
    					, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
    					, ALL(TransType), TransType[QtySign]<>0
    					)
    				,3)
    	
    			  * IF(ISFILTERED(SymbolSector[Sector]) || ISFILTERED(SymbolSector[Sensitivity]), CALCULATE(SUM(SymbolSector[Percent])), 1)
    			  * CALCULATE(
    					IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), VALUES(Quotes[Close])
    						, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, SUM(Quotes[Price1]), 2, SUM(Quotes[Price2]), 3, SUM(Quotes[Price3]), 4, SUM(Quotes[Price4]), 5, SUM(Quotes[Price5]), SUM(Quotes[Close]) )
    					)
    					, LASTNONBLANK(
    						DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), MAX(Dates[Date]))
    					  , CALCULATE(COUNT(Quotes[Close]) )) 
    				)
    		)
    , 2)
    I will send you back PowerBI desktop with this change. Basically, all I fix is change VALUES to SUM in the place that returns last price.

  • Hi Vidas, I will look for the undefined symbols. Thank you for your effort, it is much appreciated :)
Sign In or Register to comment.