Forum Discussion

Ramachandran's avatar
Ramachandran
Helper III
4 years ago
Solved

Multiple column cannot converted into scalar value

Hi, I am trying to retrieve get topn sum of values using category on visual but I'm getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Ramachandran , Try like

     

    Sumof TopN values =
    var ProductstoRank = 'TopN'[TopN Value]
    var IsOtherSelected = SELECTEDVALUE('Source Product Category Name'[Product Category.Product Category]) = "Top Total"
    var IsOtherSelectedPer = SELECTEDVALUE('Source Product Category Name'[Product Category.Product Category]) = "% of Total"
    var ProductwithSalesAmt =
    ADDCOLUMNS(
    ALLSELECTED('Source Product Category Name'[Product Category.Product Category]),
    "@Amt", 'SMI Data Miner'[GSMData]
    )
    var Top3Prods = TOPN(ProductstoRank, ProductwithSalesAmt, [@Amt])
    var SalesofTop3 = calculate( SUMX(ProductwithSalesAmt, [@Amt]) ,TOPN(ProductstoRank, ProductwithSalesAmt, [@Amt]))

amitchandak's avatar
amitchandak
Super User
4 years ago

Ramachandran , Try like

 

Sumof TopN values =
var ProductstoRank = 'TopN'[TopN Value]
var IsOtherSelected = SELECTEDVALUE('Source Product Category Name'[Product Category.Product Category]) = "Top Total"
var IsOtherSelectedPer = SELECTEDVALUE('Source Product Category Name'[Product Category.Product Category]) = "% of Total"
var ProductwithSalesAmt =
ADDCOLUMNS(
ALLSELECTED('Source Product Category Name'[Product Category.Product Category]),
"@Amt", 'SMI Data Miner'[GSMData]
)
var Top3Prods = TOPN(ProductstoRank, ProductwithSalesAmt, [@Amt])
var SalesofTop3 = calculate( SUMX(ProductwithSalesAmt, [@Amt]) ,TOPN(ProductstoRank, ProductwithSalesAmt, [@Amt]))