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 value."

 

For your reference, the code is below and I'm trying to get data from cube source

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 = SUMX(Top3Prods, [@Amt])

Return Top3Prods

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

1 Reply

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